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

MORE INFORMATION

If you pass date values, time values, or timestamp values in a remote query to an SQL table, 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 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 date 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 date values, the time values, or the timestamp values are formatted the way that you want them in the Examples field of the Selection Criteria tab. When you save the view, the information in the Examples field is saved together with the view. If you open and then modify the view, the information in 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 a remote query instead of passing a value, you do not experience an ODBC error because Visual FoxPro performs a conversion.