You are currently offline, waiting for your internet to reconnect

How to format a date or time value to query an SQL table in Visual FoxPro

This article was previously published under Q145817
In Visual FoxPro, you can format date type input data as {mm/dd/yy}. Or, ifyou type directly in a date field, you can format date type input data as mm/dd/yy. However, if you format the data as {mm/dd/yy} or as mm/dd/yy when you perform aremote query on an SQL table, you experience an ODBCerror.
If you pass date values, time values, or timestamp values in a remote query to an SQLtable, you have to format the data by using the following escape clauses:
  • Date value: {d 'yyyy-mm-dd'}. In this format, yyyy represents the year, mm represents the month, and dd represents the day.
  • Time value: {t 'hh:mm:ss'}. In this format, hh represents the hours, mm represents the minutes, and ss represents the seconds.
  • Timestamp value: {ts 'yyyy-mm-ddhh:mm:ss'}.
In a SQL pass-through query, you can use the following syntax to retrievethe 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 queriesdate and time 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, make sure that the datevalues, the timevalues, or the timestampvalues are formatted the way that you want them in the Examples field of the SelectionCriteria tab. When you save the view, the information in the Examples fieldis saved together with the view. If you open and then modify the view, the informationin the Examples field is lost. If you do this, you must type the information again.
Note When you pass the field name as a parameter in aremote query instead of passing a value, you do not experience an ODBC error because Visual FoxProperforms a conversion.
ODBC 2.0 Programmer's Reference and SDK Guide, pp.50-52, Microsoft Press.

Article ID: 145817 - Last Review: 02/12/2007 18:29:26 - Revision: 4.4

Microsoft Visual FoxPro 9.0 Professional Edition, Microsoft Visual FoxPro 8.0 Professional Edition, Microsoft Visual FoxPro 7.0 Professional Edition, Microsoft Visual FoxPro 6.0 Professional Edition, Microsoft Visual FoxPro 3.0b Standard Edition, Microsoft Visual FoxPro 3.0 Standard Edition

  • kbhowto kbinterop KB145817