Visual FoxPro ODBC Driver query fails if the date format is not the AMERICAN date format (229854)


The information in this article applies to:

  • Microsoft ODBC Driver for Visual FoxPro (Build 6.01.8629.01) 6.0
  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft Visual FoxPro for Windows 3.0b
  • Microsoft Visual FoxPro for Windows 5.0
  • Microsoft Visual FoxPro for Windows 5.0a
  • Microsoft Visual FoxPro for Windows 6.0
  • Microsoft Visual FoxPro for Windows 7.0
  • Microsoft Visual FoxPro 8.0
  • 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:
Date SettingDate Format
AMERICANmm/dd/yy
ANSIyy.mm.dd
BRITISH/FRENCH dd/mm/yy
GERMANdd.mm.yy
ITALIANdd-mm-yy
JAPANyy/mm/dd
TAIWANyy/mm/dd
USAmm-dd-yy
MDYmm/dd/yy
DMYdd/mm/yy
YMDyy/mm/dd
SHORTShort date format determined by the Windows Control Panel short date setting.
LONGLong date format determined by the Windows Control Panel long date setting.

Steps to Reproduce Behavior

  1. 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 < {07/22/93}"
lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {93/07/22}"
*!* Create An ADO recordset
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
  2. Observe that no records are returned or displayed.
  3. Comment the following line of code:
    lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {93/07/22}"
  4. Uncomment the following line of code:
    lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {07/22/93}"
  5. Re-run the program and observe that data are returned and displayed on the screen.
Modification Type:MajorLast Reviewed:3/18/2005
Keywords:kbDatabase kbprb KB229854
©2004 Microsoft Corporation. All rights reserved.