HOWTO: Formatting a Date or Time Value to Query an SQL Table

 Last reviewed: May 19, 1997
Article ID: Q145817
The information in this article applies to:
  • Microsoft Visual FoxPro for Windows, versions 3.0 and 3.0b

SUMMARY

In Visual FoxPro, you can format date type input data as {mm/dd/yy}, or, if you type directly in a date field, as mm/dd/yy. However, when you perform a remote query on an SQL table and format the data in this fashion, an ODBC error is the result.

MORE INFORMATION

If, in a remote query, you pass date, time, or timestamp values to an SQL table, you need to format the data with the following escape clauses:

  • Date value: {d 'yyyy-mm-dd'}, where yyyy represents the year, mm the month, and dd the day.
  • Time value: {t 'hh:mm:ss'}, where hh represents the hours, mm the minutes, and ss the seconds.
  • Timestamp value: {ts 'yyyy-mm-dd hh:mm:ss'}.

In a SQL Pass Through command, you can use the following syntax to retrieve the value of a date field: 

   =SQLEXEC( nConnHandle, "SELECT * FROM TITLES WHERE
      Titles.pubdate<{ts '1995-06-12 12:55:00'}", 'MyCursor')
You can use the following syntax to create a remote view that queries datetime information: 

   CREATE SQL VIEW sqldate REMOTE CONNECTION sqldate AS SELECT * FROM ;
      dbo.titles WHERE Titles.pubdate<{ts '1985-06-12 12:55:00'}
In the View Designer, ensure that the date, the time, or the timestamp values are formatted as needed in the Examples field of the Selection Criteria tab. Once the view is saved, the information in the Examples field is saved with the view. If the view is opened and modified, the information disappears and has to be typed back in the Examples field.

Note that when you pass the field name (not a value) as a parameter to a remote query, you do not receive an ODBC error because Visual FoxPro performs a conversion.

REFERENCES

ODBC 2.0 Programmer's Reference and SDK Guide, pp.50-52, Microsoft Press.

Keywords : FxinteropOdbc kbinterop vfoxwin
Version : 3.0 3.0b
Platform : WINDOWS
Issue type : kbhowto

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.

Last reviewed: May 19, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.