PRB: ODBC Query Fails with Non-American Date Format

ID: Q229854


The information in this article applies to:
  • Microsoft Data Access Components versions 2.0, 2.1
  • Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0


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 SQL Select 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 Setting Date Format
AMERICAN mm/dd/yy
ANSI yy.mm.dd
BRITISH/FRENCH dd/mm/yy
GERMAN dd.mm.yy
ITALIAN dd-mm-yy
JAPAN yy/mm/dd
TAIWAN yy/mm/dd
USA mm-dd-yy
MDY mm/dd/yy
DMY dd/mm/yy
YMD yy/mm/dd
SHORT Short date format determined by the Windows Control Panel short date setting.
LONG Long 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:


  2. 
    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"
    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  
  3. Observe that no records are returned or displayed.


  4. Comment the following line of code:


  5. 
    lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {93/07/22}" 
  6. Uncomment the following line of code:


  7. 
    lcSQL="SELECT * FROM ORDERS WHERE ORDER_DATE < {07/22/93}" 
  8. Re-run the program and observe that data are returned and displayed on the screen.


Additional query words:

Keywords : kbDatabase kbMDAC kbODBC kbVFp600 KbDBFDBC kbGrpFox kbDSupport
Version : WINDOWS:2.0,2.1,3.0,3.0b,5.0,5.0a,6.0
Platform : WINDOWS
Issue type : kbprb


Last Reviewed: July 1, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.