DOCUMENT:Q176572  14-AUG-2001  [foxpro]
TITLE   :FIX: ODBC Date Filter Greater Than 12/31/1999 Return No Records
PRODUCT :Microsoft FoxPro
PROD/VER::5.0
OPER/SYS:
KEYWORDS:kbYear2000 kbDriver kbvfp500 kbVS600fix kbGrpDSFox kbGrpDSMDAC

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft ODBC Driver for Visual FoxPro, version 5.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

A SQL query through the Microsoft Visual FoxPro driver (Vfpodbc.dll) that
specifies a date greater than 12/31/1999 in the WHERE statement does not return
any records.

RESOLUTION
==========

Use one of the following workarounds:

 - Use the Visual FoxPro syntax {01/01/2000} instead of the ODBC escape sequence
   dates {d '01/01/2000'}.

 - Use the CTOD() function to convert the date string to a date. For example:

   SELECT * FROM Test WHERE MyDate BETWEEN CTOD('01/01/1997');
            AND CTOD('01/01/2002')

 - Pass the date as a parameter instead of an embedded string. For example:

   SELECT * FROM Test WHERE MyDate BETWEEN ? and ?

   NOTE: The method only works if the application you are using in the front end
   supports passing parameters to the ODBC driver using the SQLBindParameter
   ODBC API.

STATUS
======

Microsoft has confirmed this to be a bug in the Microsoft products listed at the
beginning of this article.This bug was corrected in the Vfpodbc.dll file that
ships with Visual FoxPro 6.0..

The fix is available in Microsoft Visual Studio 6.0 and through the download of
the Microsoft Data Access Components (MDAC) version 2.5. You can download MDAC
2.5 from the following Microsoft Web site:

   http://www.microsoft.com/data/

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.

Additional query words: Date

======================================================================
Keywords          : kbYear2000 kbDriver kbvfp500 kbVS600fix kbGrpDSFox kbGrpDSMDAC 
Technology        : kbVFPsearch kbAudDeveloper kbODBCSearch kbODBCVFP500
Version           : :5.0
Issue type        : kbbug
Solution Type     : kbfix

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.