Strange SQL date behaviour via ODBC

I am pulling data from visual foxpro tables using the "Microsoft Visual
foxpro ODBC driver". There seems to be a problem with the date fields.

A Select statement always includes information from date proir to the date set
as the start date of the date range . i.e 
CashStartdate = ctod("05/03/2008")
CashEnddate = ctod("15/03/2008")
the data in result will starts include records that are in the range 
"04/03/2008" and end on "15/03/2008"


*Sample of code
d = new Database()
d.databaseName = "Salesarchive"
d.active = true
            
q = new Query()
q.database = d
q.sql = [select * from "allsales" allsales where (transno > 0 or
type = "AP") ] + ;
          [and allsales.'date' >= :ParmStartdate  and allsales.'date' <=
:ParmEnddate ]
 q.params[ "ParmStartdate" ] = CashStartdate
 q.params[ "ParmEnddate" ] = CashEnddate

Tom;
I tested code similar to yours, modified for MM/DD/YYYY dates. For 
simplicity I left out fields Transno and Type.
I got the correct result: dates between March 5 and March 15, 2008.
I have VFP version 6.
The ODBC connection uses a system DSN with a free table directory.
The ODBC driver is 'Microsoft Visual Foxpro Driver' 6.00.8167.00.
Eric Logan



">I am pulling data from visual foxpro tables using the "Microsoft
Visual 
foxpro ODBC driver". There seems to be a problem with the date fields.
> A Select statement always includes information from date proir to the date

> set as the start date of the date range . i.e
> CashStartdate = ctod("05/03/2008")
> CashEnddate = ctod("15/03/2008")
> the data in result will starts include records that are in the range 
> "04/03/2008" and end on "15/03/2008"
> *Sample of code
> d = new Database()
> d.databaseName = "Salesarchive"
> d.active = true
> q = new Query()
> q.database = d
> q.sql = [select * from "allsales" allsales where (transno > 0
or type = 
> "AP") ] + ;
>          [and allsales.'date' >= :ParmStartdate  and allsales.'date'
<= 
> :ParmEnddate ]
> q.params[ "ParmStartdate" ] = CashStartdate
> q.params[ "ParmEnddate" ] = CashEnddate

Tom,

If they follow the SQL standard, dates are mm/dd/yyyy.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

In article <MPG.2264d709900d2da498a073@news.dbase.com>, 
gswassREMOVE_ME@attglobal.net says...
> Tom,
> 
> If they follow the SQL standard, dates are mm/dd/yyyy.

That is Access' standard.  The SQL standard is yyyy-mm-dd or yyyymmdd.

-- 
Remove the ns_ from if replying by e-mail (but keep posts in the 

>> If they follow the SQL standard, dates are mm/dd/yyyy.
> That is Access' standard.  The SQL standard is yyyy-mm-dd or yyyymmdd.

Tom is using dBase to retrieve data from FoxPro tables through ODBC.
What standard would be relevant?
(Whatever works.)
E.L.