Microsoft KB Archive/145817

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

Article ID: 145817

Article Last Modified on 2/12/2007

-

APPLIES TO


 * 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

-



This article was previously published under Q145817



INTRODUCTION
In Visual FoxPro, you can format date type input data as {mm/dd/yy}. Or, if you 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 a remote query on an SQL table, you experience an ODBC error.



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 ' - - '}. In this format,  represents the year,   represents the month, and   represents the day.
 * Time value: {t ' : : '}. In this format,  represents the hours,   represents the minutes, and   represents the seconds.
 * Timestamp value: {ts ' - - : : '}.

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.

