Microsoft KB Archive/308133

= CONVERT function gets date from SQL Server DateTime field with a Visual FoxPro query =

Article ID: 308133

Article Last Modified on 2/2/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q308133



SUMMARY
Dates that are stored in a SQL Server table are stored as a DateTime data type. You can use the SQL Server CONVERT function to extract the date portion of a SQL DateTime field with a Visual FoxPro query; for example: SELECT CONVERT(CHAR(10),, 101) FROM You can still supply just the date when specifying a filter condition in the query. The time is not required in the expression. For example: SELECT CONVERT(CHAR(10),, 101) FROM ; WHERE = '01-01-1999' The first and third parameters in the function call above (CHAR(10) and 101) are specific to extracting Date from DateTime. The SQL Server Books Online topic for the CONVERT function describes other available options.



MORE INFORMATION
The following Visual FoxPro sample code connects to SQL Server and retrieves data. The code uses the SQL CONVERT function to extract the date part of the DateTime field &quot;ORD_DATE&quot;. Note that the data type returned by the CONVERT command is CHARACTER in Visual FoxPro.

To use this sample, follow these steps:   Paste the following code in a new program inside Visual FoxPro: *!* Enter your specific SQL Server information here.
 * 1) DEFINE   SQL_NAME    &quot; &quot;
 * 2) DEFINE   SQL_UID     &quot; &quot;
 * 3) DEFINE   SQL_PWD     &quot; &quot;

LOCAL lcSQLConnStr, ; lnSQLConnHandle, ; lnSQLExecSuccess

lcSQLConnStr = &quot;DRIVER={SQL Server};SERVER=&quot; + SQL_NAME + ; &quot;;DATABASE=PUBS;UID=&quot; + SQL_UID + &quot;;PWD=&quot; + SQL_PWD

lnSQLConnHandle = SQLSTRINGCONNECT(lcSQLConnStr) IF lnSQLConnHandle < 1 LOCAL laErrArray[1] AERROR(laErrArray) WAIT WINDOW &quot;Unable to connect:&quot; + CHR(13) + laErrArray[3] RETURN .F. ENDIF

lnSQLExecSuccess = SQLEXEC(lnSQLConnHandle, &quot;SELECT stor_id, ord_num, ;          CONVERT(CHAR(10), ord_date, 101) AS ord_date, qty, ;           payterms, title_id from sales&quot;, &quot;RESULTS&quot;)


 * !* Note that you can still use just a date value if using a WHERE clause
 * !* to filter the data:
 * !* lnSQLExecSuccess = SQLEXEC(lnSQLConnHandle, &quot;SELECT stor_id, ord_num, ;
 * !*        CONVERT(CHAR(10), ord_date, 101) AS ord_date, qty, payterms, ;
 * !*        title_id from sales WHERE ord_date < '10/28/1993'&quot;, &quot;RESULTS&quot;)

IF lnSQLExecSuccess < 1 LOCAL laErrArray[1] AERROR(laErrArray) WAIT WINDOW &quot;SQLEXEC Failed:&quot; + CHR(13) + laErrArray[3] ENDIF

SQLDISCONNECT(lnSQLConnHandle)

IF SELECT(&quot;RESULTS&quot;) > 0 SELECT RESULTS BROWSE NOWAIT ENDIF  Enter your SQL Server name, user ID, and password at the top of the code where indicated. Save and run the code.

