Microsoft KB Archive/229854

= Visual FoxPro ODBC Driver query fails if the date format is not the AMERICAN date format =

Article ID: 229854

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Open Database Connectivity Driver for Visual FoxPro 5.0
 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q229854



SYMPTOMS
When executing an ODBC query against Visual FoxPro tables using the Visual FoxPro ODBC Driver, no records are returned when the WHERE clause includes a date that is not in AMERICAN date format.



CAUSE
The Visual FoxPro ODBC Driver only accepts dates in a strict AMERICAN date format.



RESOLUTION
Convert any dates that are passed in WHERE clause of the SELECT-SQL statement to an AMERICAN date format.



STATUS
This behavior is by design.



MORE INFORMATION
The default Visual FoxPro date setting is AMERICAN. Date formats, however, may be set to the following formats:

Steps to Reproduce Behavior
  Create a program file named "Odbctest.prg," using the following code: CLEAR DO CASE CASE "6.0"$VERSION lcConnStr="DRIVER={Microsoft Visual FoxPro Driver};" + ; "Exclusive=No;SourceType=DBF;SourceDB="+HOME(2)+"DATA" CASE "5.0"$VERSION lcConnStr="DRIVER={Microsoft Visual FoxPro Driver};" + ; "Exclusive=No;SourceType=DBF;SourceDB="+HOME+"SAMPLES\DATA" CASE "3.0"$VERSION lcConnStr="DRIVER={Microsoft Visual FoxPro Driver};" + ; "Exclusive=No;SourceType=DBF;SourceDB="+HOME+"SAMPLES\DATA" OTHERWISE && Version is VFP 7.0,8.0, or 9.0 lcConnStr="DRIVER={Microsoft Visual FoxPro Driver};" + ; "Exclusive=No;SourceType=DBF;SourceDB="+HOME(2)+"DATA" ENDCASE


 * !* Create An ADO Connection

oConnection=CREATEOBJECT("ADODB.Connection") oConnection.ConnectionString = lcConnStr oConnection.CursorLocation  = 3 oConnection.OPEN

lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {93/07/22}" rs=CREATEOBJECT("ADODB.Recordset") rs.activeconnection        = oConnection rs.CursorLocation          = 3 rs.cursortype              = 1 rs.LockType                = 3 rs.OPEN(lcSQL) IF !rs.EOF rs.movefirst DO WHILE !rs.EOF ? rs.FIELDS(0).VALUE rs.movenext ENDDO ENDIF rs.CLOSE oConnection.CLOSE  Observe that no records are returned or displayed.  Comment the following line of code: lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {93/07/22}"   Uncomment the following line of code: lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {07/22/93}"  Re-run the program and observe that data are returned and displayed on the screen.</li></ol>
 * lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {07/22/93}"
 * !* Create An ADO recordset

Keywords: kbdatabase kbprb KB229854

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.