Problems with BETWEEN clause

[codefighta]

Hello,

The sql query (see below) containing a BETWEEN clause does not work. See errors below:

SELECT sum(cost), desc, num, sum(qty), serialnum FROM itsim204.dbf WHERE type=6 AND (date BETWEEN '02/01/2004' AND '02/29/2004') AND serialnum='23651A' GROUP by num

[message] => DB Error: invalid number
[nativecode=22005 [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]

Any suggestions to get this working?
Thanks
cf

 

[Stella740pl]

codefighta

Your error is Operator/operand type mismatch, and even if the error appears as you introduce the BETWEEN keyword, it, most likely, doesn't have anything to do with it.

What is the type of your date field? If it is Date, then put the values in curly brackets, not in apostrophes:

BETWEEN {02/01/2004} AND {02/29/2004}

(Also, it's not necessary to put the BETWEEN expression in parenthesis, but wouldn't hurt, either).

What types are your [/i]type[/i] and serialnum fields? Check those, either.

 

[mspratt]

Hi cf,

(date BETWEEN '02/01/2004' AND '02/29/2004') should be
BETWEEN(date, {02/01/2004}, {02/29/2004})

Regards,

Mike

 

[codefighta]

Hi Stella740pl!

Thanks for your quick response!
It Works!!

Looking at the data dictionary of the app whose tables I am trying to access the types are as follows:

date = DPDT
type = CINT
serialnum = CSTR

I am absolutly not familiar with FoxPro and don't know if these are actually FoxPro field types or if these are just part of an internal naming convention of the company who wrote the app.

Is the curly bracket syntax a "FoxPro thing". Never seen that before.

Thanks
cf

 

[Stella740pl]

What are the file extensions? Where are they located?

I am not familiar with this terminology before, plus the error message mentions ODBC Visual FoxPro Driver, so it could be some other database files are accessed through FoxPro, or some internal naming convention is in place. But it's understandable: Date, Integer, and Character string. So, while in FoxPro, the curly bracket syntax goes for the dates. Apostrophes, or quotation marks, or square brackets are used for character strings.

Alternatively, you could use for the dates

BETWEEN DATE(2004,02,02) AND DATE(2004,02,29)

and couple more ways.

 

[codefighta]

To give you the full picture:

I am working in PHP using PEAR:B to access FoxPro (.dbf) files on another server (the application I am writing actually is based on mySql, but I need data from other sources from time to time).
The errors are generated by the odbc wrapper in PEAR:B.

Thanks for your help!
You might be seeing more posts of mine here in the next few days as I try to grasp the syntax for getting stuff from FoxPro (very different from mySql syntax).

Thanks
cf

 

[Olaf Doschke]

To add, I'd say the SQL BETWEEN should be faster as the BETWEEN function. The most general way of date or datetime constants is {^YYYY-MM-DD} or {^YYYY-MM-DD HH:MM:SS}. SQL Server accepts that, too, I think.

Bye, Olaf.

 

[Stella740pl]

Olaf,

SQL BETWEEN should be faster as the BETWEEN function
Really? Did you run tests or read some info? I've asked this question here once, but the results were not conclusive. Personally, I tend now to use BETWEEN(), INLIST(), etc. functions instead of corresponding SQL syntax, because I often have to build conditions as go with the choices made, and if I have something like

cond1="!BETWEEN(date, d1, d2)"
cond2="INLIST(DOW(date),1,7)"

instead of

cond1="date NOT BETWEEN d1 AND d2"
cond2="DOW(date) IN (1,7)",

I can use it for both, SQL and non-SQL syntax:

SELECT * FROM something WHERE &cond1 AND &cond2 INTO ...
and
DELETE ALL FOR &cond1 AND &cond2

but with SQL syntax, I would have to keep double set of variables.

Maybe you can comment on thread184-767158 or right here? Thanks.

Stella

 

[Olaf Doschke]

Hi Stella,

I've read the other thread and your tests.
Well, I think I read it somewhere, that SQL Between is faster. The argument was, that there is no extra call to a vfp function, so it remains in the sql engine to interprete the select.

Maybe that does not give a big difference, but sql that doesn't use foxpro functions is of course better portable to other databases. Maybe that's the point in such situations where the gain or loss in performance is negligibly (neglectable?).

Bye, Olaf.

 

[Stella740pl]

Thanks for the comments, Olaf!

Stella