SQL for Fox Dates 6

[RonRepp]

Hi all:

I had posted in regular VB section and they pointed me here with my problem.

Hi all:

I don't know where else to post.

I need a SQL statement that will pull data from a Fox table between certain dates. Unfortunately, Between() in Fox is a key command.

This is what I have:

        SQL = "Set exclusive off; SELECT * FROM DAILY1.dbf WHERE [Date] BETWEEN CTOD ('" & sDate & "') AND CTOD ('" & eDate & "') AND Key = '" & Seg & "' "

''This give me an error

I've tried >= and <= to no avail.

Any help will be greatly appreciated.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[bborissov]

Ron,
Remove square bracjets from your SELECT. VFP is not like SQL Server.

SQL = "Set exclusive off; SELECT * FROM DAILY1 WHERE Date BETWEEN CTOD ('" & sDate & "') AND CTOD ('" & eDate & "') AND Key = '" & Seg & "' "

Can you tell me how sDate and eDate look like?
Because you could have a problems with CTOD() function if they are not in proper format. You could use:

SQL = "Set exclusive off; SELECT * FROM DAILY1 WHERE DTOS(Date) BETWEEN '" & sDate & "' AND '" & eDate & "' AND Key = '" & Seg & "' "

IF both variables are in YYYYMMDD format.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Olaf Doschke]

Hello Ron,

I see several problems, so no wonder you grew gray hair...

First, in FOX ";" is not end of a command but concatiination of multiline commands (like "_" in VB).

So I'd rather define if working exclusive = off or on once in the connection to the database, afterward you only need the SQL-Select.

Second, [Date] will be interpreted as the string "Date" in FOX, to adress fields, simply write their names, field names never can contain spaces in FOX.

sDate and eDate should be string vars holding the date in whatever format CTOD() needs to convert them to a real date. That depends on language and FOX's Date setting. I'd reccommend a notation independent of such settings, which is {^YYYY-MM-DD}

sDate = "{^2006-01-01}"
eDate = "{^2006-12-31}"
SQL = "SELECT * FROM DAILY1.dbf WHERE Date BETWEEN " & sDate & " AND " & eDate & " AND Key = '" & Seg & "' "

Bye, Olaf.

 

[RonRepp]

Hi:

Thanks for the replies.

Unfortunately, Date is the field name within the table. This is my boss's great idea. This is why I put it in [Brackets], because otherwise it thinks that it is DATE.

I don't know. I'm not much of a VFP person.

The sDate comes through as a regular date of 1/1/2007. This can be manipulated if need be.

I hope that I answered enough questions for you guys to help, because I'm sitting on about a 30 minute query that should take literally 5 seconds.

Thanks,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[dgrewe]

If the Query is taking 30 minutes, Have a Index added to the FOX table on Field DATE.


David W. Grewe Dave

 

[bborissov]

No matter if the Date is field you could use it w/o square brackets, better if it is not akeyword, but you still could use it.

1/1/2007 is what?
DD/MM/YYYY
or
MM/DD/YYYY

Could you manage to have YYYYMMDD strung from these two variables? If so you could use my first suggestion:

SQL = "SELECT * FROM DAILY1 WHERE DTOS(Date) BETWEEN '" & sDate & "' AND '" & eDate & "' AND Key = '" & Seg & "' "

Nut in that case you should have an index by DTOS(Date) in that table.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Stella740pl]

Ron, even though naming a table field Date is not a great idea, I have some of those in some old applications with no problems - and no brackets or delimiters of any kind needed around them.

In the context of a query, Date without any delimiter will be interpreted as a field name or a variable, nothing else.

What Borislav and Olaf were saying is that square brackets in FoxPro are equivalent to single quotes and double quotes and used to delimit a character string, so if you use square brackets here as you would in SQL server, then FoxPro would would interpret [Date] as a character string with value equal to "Date". Drop the brackets from around the field name.

 

[Imaginecorp]

Or:
***if coding in Fox
Set Exclusive on
sDate = DATE(2006,7,31)
eDate = DATE(2007,7,31)
Seg = "whatever" (quotes) if Character, Whatever (no
quotes) if Numeric

SELECT * FROM DAILY1 WHERE (DATE() BETWEEN sDate AND eDate) AND Key = seg

DATE() = todays date

 

[Imaginecorp]

It does not matter if "Date" is the name of the field, just add the table name to it i.e. if table name = daily1 then

SELECT * FROM DAILY1 WHERE (daily1.date BETWEEN sDate AND eDate) AND Key = seg

 

[RonRepp]

I get this error:

Additional information: [Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.

SELECT Date, SUM(CallTime), SUM(Orders),SUM(items),SUM(refusals),SUM(donotcall),SUM(unworkable), SUM(completed),SUM(used) FROM DAILY1.DBF WHERE WHERE Daily1.Date) BETWEEN ctod('" & sDate & "') AND ctod('" & eDate & "'

Boss is riding me; does anyone else have a suggestion?

I'm pretty sure the error is coming from the BETWEEN keyword, but I'm not positive.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[bborissov]

You forgot the last closing bracket:

....
 ctod('" & eDate & "'[b][COLOR=red])"[/color][/b]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[TamarGranor]

If what you're showing is your exact query, the problem is that the keyword WHERE appears twice in a row.

Tamar

 

[Stella740pl]

You have WHERE here twice, and then an unneeded parenthesis after FROM DAILY1.DBF WHERE WHERE Daily1.Date).

 

[RonRepp]

Thanks to all of you, I can now start plucking my boss's teeth out of my rear end.

You've been lifesavers.

Thanks,



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[SBTBILL]

Just as an alternate I put the entire ADO command in a string, use ADO to open the table and then execute the string.

Seems to work fine.

Looks like you are pulling the data from a DBF. In that case I'd use native VFP as it is usualy faster, particularly if you have an index you can seek, on though dates can be a bit of a pain becuse you sometime have to loop and increment to find the first day. Also why not table.date or &table..date. It's old fashioned but it works.

Bill Couture
http:\\

http://www.sbtbill.com

Bill Couture
http:\\

http://www.sbtbill.com