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

Article translations Article translations
Article ID: 145817 - View products that this article applies to.
This article was previously published under Q145817
Expand all | Collapse all

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 '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.

REFERENCES

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

Properties

Article ID: 145817 - Last Review: February 12, 2007 - Revision: 4.4
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
Keywords: 
kbhowto kbinterop KB145817

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com