ODBC Scalar Functions

Microsoft Jet SQL Reference

ODBC Scalar Functions

Microsoft® Jet SQL supports the use of the ODBC defined syntax for scalar functions. For example, the query:

SELECT DAILYCLOSE, DAILYCHANGE FROM DAILYQUOTE
WHERE {fn ABS(DAILYCHANGE)} > 5

Would return all rows where the absolute value of the change in the price of a stock was greater than five.

A subset of the ODBC defined scalar functions is supported. The following table lists the functions that are supported.

For a description of the arguments and a complete explanation of the escape syntax for including functions in a SQL statement, see the ODBC documentation.

String Functions
ASCII LENGTH RTRIM
CHAR LOCATE SPACE
CONCAT LTRIM SUBSTRING
LCASE RIGHT UCASE
LEFT    

Numeric Functions
ABS FLOOR SIN
ATAN LOG SQRT
CEILING POWER TAN
COS RAND MOD
EXP SIGN  

Time & Date Functions
CURDATE DAYOFYEAR MONTH
CURTIME YEAR WEEK
NOW HOUR QUARTER
DAYOFMONTH MINUTE MONTHNAME
DAYOFWEEK SECOND DAYNAME

Data Type Conversion
CONVERT String literals can be converted to the following data types: SQL_FLOAT, SQL_DOUBLE, SQL_NUMERIC, SQL_INTEGER, SQL_REAL, SQL_SMALLINT, SQL_VARCHAR and SQL_DATETIME.

See Also

Configuring the Microsoft Jet Database Engine for ODBC Access