You can use ODBC escape syntax from any ODBC application. This escape syntax allows you to call a set of common functions regardless of the database management system you are using. The general form for the escape syntax is
{ keyword parameters }
The set of keywords includes the following:
You can use the escape syntax to access a library of functions implemented by the ODBC driver that includes number, string, time, date, and system functions.
For example, to obtain the current date in a database management system-neutral way, you would execute the following:
SELECT { FN CURDATE() }
The following tables list the functions that are supported by the SAP Sybase IQ ODBC driver.
Numeric functions | String functions | System functions | Time/date functions |
---|---|---|---|
ABS | ASCII | DATABASE | CURDATE |
ACOS | BIT_LENGTH | IFNULL | CURRENT_DATE |
ASIN | CHAR | USER | CURRENT_TIME |
ATAN | CHAR_LENGTH | CONVERT | CURRENT_TIMESTAMP |
ATAN2 | CHARACTER_LENGTH | CURTIME | |
CEILING | CONCAT | DAYNAME | |
COS | DIFFERENCE | DAYOFMONTH | |
COT | INSERT | DAYOFWEEK | |
DEGREES | LCASE | DAYOFYEAR | |
EXP | LEFT | EXTRACT | |
FLOOR | LENGTH | HOUR | |
LOG | LOCATE | MINUTE | |
LOG10 | LTRIM | MONTH | |
MOD | OCTET_LENGTH | MONTHNAME | |
PI | POSITION | NOW | |
POWER | REPEAT | QUARTER | |
RADIANS | REPLACE | SECOND | |
RAND | RIGHT | TIMESTAMPADD | |
ROUND | RTRIM | TIMESTAMPDIFF | |
SIGN | SOUNDEX | WEEK | |
SIN | SPACE | YEAR | |
SQRT | SUBSTRING | ||
TAN | UCASE | ||
TRUNCATE |
The ODBC driver maps the TIMESTAMPADD and TIMESTAMPDIFF functions to the corresponding database server DATEADD and DATEDIFF functions. The syntax for the TIMESTAMPADD and TIMESTAMPDIFF functions is as follows.
{ fn TIMESTAMPADD( interval, integer-expr, timestamp-expr ) }
Returns the timestamp calculated by adding integer-expr intervals of type interval to timestamp-expr. Valid values of interval are shown below.
{ fn TIMESTAMPDIFF( interval, timestamp-expr1, timestamp-expr2 ) }
Returns the integer number of intervals of type interval by which timestamp-expr2 is greater than timestamp-expr1. Valid values of interval are shown below.
interval | DATEADD/DATEDIFF date-part mapping |
---|---|
SQL_TSI_YEAR | YEAR |
SQL_TSI_QUARTER | QUARTER |
SQL_TSI_MONTH | MONTH |
SQL_TSI_WEEK | WEEK |
SQL_TSI_DAY | DAY |
SQL_TSI_HOUR | HOUR |
SQL_TSI_MINUTE | MINUTE |
SQL_TSI_SECOND | SECOND |
SQL_TSI_FRAC_SECOND | MICROSECOND - The DATEADD and DATEDIFF functions do not support a resolution of nanoseconds. |
The ODBC escape syntax is identical to the JDBC escape syntax. In Interactive SQL, which uses JDBC, the braces must be doubled. There must not be a space between successive braces: "{{" is acceptable, but "{ {" is not. As well, you cannot use newline characters in the statement. The escape syntax cannot be used in stored procedures because they are not parsed by Interactive SQL.
For example, to obtain the number of weeks in February 2013, execute the following in Interactive SQL:
SELECT {{ fn TIMESTAMPDIFF(SQL_TSI_WEEK, '2013-02-01T00:00:00', '2013-03-01T00:00:00' ) }}