Microsoft KB Archive/103576

= SQL Server Time/Date Func Support for Connectivity Kit =

Q103576

-

The information in this article applies to:


 * Microsoft Visual FoxPro for Windows, version 3.0
 * Microsoft FoxPro Connectivity Kit for Windows, version 2.5, 2.5a
 * Microsoft FoxPro Connectivity Kit for MS-DOS, version 2.5, 2.5a

-

SUMMARY
The Microsoft FoxPro Connectivity Kit can be used with FoxPro versions 2.5 and 2.5a for MS-DOS and Windows. The FoxPro Connectivity Kit, and the Open Database Connectivity (ODBC) specification in general, supports the minimum ANSI SQL standard. Below is a list of the SQL date and time functions that are supported.

MORE INFORMATION
The following table lists the supported date and time functions.

Parameter arguments listed as DATE_EXP can be a date-type column name, a date literal, or a date type result from another function call.

Parameter arguments listed as TIME_EXP can be a column name, a time literal, a timestamp, or results from another function call.

  Function                  Description --

NOW                    Returns the current date and time as a                             timestamp value.

CURDATE                Returns the current date.

DAYOFMONTH(date_exp)     Returns the day of the month in DATE_EXP as an integer value ranging from 1-31.

DAYOFWEEK(date_exp)      Returns the day of the week in DATE_EXP as an integer value ranging from 1-7, where 1 refers to Sunday.

DAYOFYEAR(date_exp)      Returns the day of the year in DATE_EXP as an integer value ranging from 1-366.

MONTH(date_exp)          Returns the month in DATE_EXP as an                             integer value ranging from 1-12.

QUARTER(date_exp)        Returns the quarter in DATE_EXP as an                             integer value ranging from 1-4.

WEEK(date_exp)           Returns the week in DATE_EXP as an                             integer value ranging from 1-12.

YEAR(date_exp)           Returns the year in DATE_EXP as an                             integer value.

CURTIME                Returns the current local time as a time value.

HOUR(time_exp)           Returns the hour in TIME_EXP as an                             integer value ranging from 0-23.

MINUTE(time_exp)         Returns the minute in TIME_EXP as an                             integer value ranging from 0-59.

SECOND(time_exp)         Returns the second in TIME_EXP as an                             integer value ranging from 0-59. Following is an example of how the NOW function could be used from within FoxPro to return the current date and time into the cursor named output. This example assumes that a valid connection to an ODBC data source has already been established and that the connection handle is contained in the variable mhand.   result=DBEXEC(mhand, "SELECT {fn NOW}","output") In Visual FoxPro, use the following syntax   result=SQLEXEC(mhand, "SELECT {fn NOW}","output")