FIX: ODBC Date Filter Greater Than 12/31/1999 Return No Records

MORE INFORMATION

NOTE: This problem only occurs when you attempt to connect from a client other than Microsoft Visual FoxPro through ODBC to a Microsoft Visual FoxPro database.

This problem specifically deals with the ODBC escape sequence dates. For example:

{d '2000/01/01')
				

NOTE: This format works correctly for any dates less than 01/01/2000.

Steps to Reproduce Behavior

1. Create a table in Microsoft Visual FoxPro by running the following code in a program file:

   CREATE TABLE TEST (MyID c(10), MyDate d)
         INSERT INTO TEST VALUES ('1',{01/01/1997})
         INSERT INTO TEST VALUES ('2',{02/01/2001})
         INSERT INTO TEST VALUES ('3',{10/22/2002})
   					

2. Create a datasource based on the Microsoft Visual FoxPro Driver with a database type of Free Table Directory. Make sure you specify the directory where Test.dbf is saved in the Path textbox.
   
   NOTE: If your application supports specifying a data source connection string, here is an example for the connect string.
   
   Replace the SOURCEDB setting with the path for the saved Test.dbf file as follows:

   DRIVER=Microsoft Visual FoxPro Driver;BACKGROUNDFETCH=NO;
         SOURCETYPE=DBF; SOURCEDB=C:\MYPATH
   					

3. Using that data source, execute the following SQL statement. This query does not return any records:

   SELECT * FROM TEST WHERE MyDate BETWEEN ;
         {d '01/01/1996'} and {d '01/01/2000'}
   						

   This query returns the one record that matches the criteria:

   SELECT * FROM TEST WHERE MyDate BETWEEN ;
         {d '01/01/1996'} and {d '12/31/1999'}
   						

   NOTE: If the application you are using does not support multi-line SQL statements without a line continuation character, please make sure to insert the appropriate line continuation character after the word BETWEEN in both SQL statements.