ODBC escape syntax

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:

{d date-string} – The date string is any date value accepted by SAP Sybase IQ.
{t time-string} – The time string is any time value accepted by SAP Sybase IQ.
{ts date-string time-string} – The date/time string is any timestamp value accepted by SAP Sybase IQ.
{guid uuid-string} – The uuid-string is any valid GUID string, for example, 41dfe9ef-db91-11d2-8c43-006008d26a6f.
{oj outer-join-expr} – The outer-join-expr is a valid OUTER JOIN expression accepted by SAP Sybase IQ.
{? = call func(p1,...)} – The function is any valid function call accepted by SAP Sybase IQ.
{call proc(p1,...)} – The procedure is any valid stored procedure call accepted by SAP Sybase IQ.
{fn func(p1,...)} – The function is any one of the library of functions listed below.

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.

SAP Sybase IQ ODBC driver supported functions

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

ODBC TIMESTAMPADD, TIMESTAMPDIFF

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.

Interactive SQL

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'  ) }}