[All]
The Complete Guide to ODBC Escape Sequences in InterBase
By: Andrew Merkulov
Abstract: ODBC Escape Sequences are SQL extension for writing SQL queries which will be compatible with many DBMS. The complete guide includes descriptions and samples for 69 scalar functions and 3 escape sequences.
The Compete Guide to ODBC Escape Sequences in Firebird and Interbase.
Authors: Piminov Roman, Merkulov Andrew IBProvider Team
Published: 04/12/2007
What are ODBC Escape Sequences?
ODBC Escape sequences have included in ODBC standard and allows write SQL queries which compatible with many DBMS. Firebird and Interbase does not contains own implementation for escape sequences and therefore we have implemented them in IBProvider and now users can write compatible SQL queries for Firebird and Interbase as for MS SQL Server, Oracle, My SQL, PostgreSQL, DB2 and etc.
At first time ODBC sequences was implemented for ODBC drivers, but bit by bit they was implemented in OLE DB drivers such as Microsoft Ole Db Provider for SQL Server, IBProvider and others.
NOTE
ODBC Escape Sequence placed in a SQL query within curly braces {escape sequence}.

For enable ODBC parser for Firebird or Interbase you must add support_odbc_query = true parameter into the IBProvider Connection String.
Why use ODBC Escape sequences?
Some SQL capabilities like calling of scalar functions or stored procedures in different DBMS implemented by different ways. But ODBC Escape Sequences define unified syntax for these operations and provide compatible and standard notation.
For the first example lets take string concatenation case. In Firebird and Oracle string concatenation has used  operator, but in MS SQL Server string concatenation has made by + operator. We must write SQL query which will be compatible with most DBMS.
String concatenation example in MS SQL:
SELECT (first_name + ' ' + last_name) as name FROM employee

String concatenation in Firebird and Oracle:
SELECT (first_name  ' '  last_name) FROM employee

Query is using escape sequence and will be working in most DBMS:
SELECT {fn concat (first_name, {fn concat (' ', last_name)})} FROM employee

For the second example lets look to the implementation of the date and time functions. First day of week can be Sunday or Monday in different DBMS or WEEK function can return different result for the first week of year. Also other different may be a reason of incorrect application behaviour. Different implementation can appears in versions on the same server.
We have implemented in IBProvider following ODBC Escape Sequences:
 Date, time and timestamp escape sequences;
 Procedure call escape sequence;
 Scalar function escape sequence;
Where can I use ODBC Escape Sequences?
 In MS SQL Server Management Studio for unified queries to Linked Servers.
 In MS SQL Server Analysis Services for math operations and analytic processing.
 In Crystal Reports and Reporting Services (SSRS) for developing DBMS insensitive reports.
 In you own application which must be SQL implementation insensitive.
Date, Time and Timestamp Escape Sequences.
Sequences name 
Syntax 
Date 
{d 'yyyymmdd'} 
Time 
{t 'hh:mm:ss'} 
Timestamp 
{ts 'yyyymmdd hh:mm:ss'} 
Sequence {t }
Example of converting date string via escape sequence:
SELECT COUNT(id) AS cnt
FROM employee
WHERE hire_date > {d '20070101'}

In the example string contains date in the escape sequence format {d yyyymmdd'}, after processing they will be converted to the DBMS specified date format.
Procedure Call Escape Sequence.
Stored procedures can be executed by different ways:
1. select * from sp_name
2. exec sp_name(params)
3. execute sp_name(params)

But you can use unified ODBC escape sequence for calling SP:
{[?=]call procedurename[([parameter][,[parameter]]...)]}

It the following example SP contains 1 input and 4 output parameters:
{call SUB_TOT_BUDGET(:dept_head, :tot_b output, :avg_b output, :min_b output, :max_b output)};

Scalar Function Escape Sequence.
ODBC standard contains own syntax for execute scalar functions:
SELECT {fn function(parameters, query fiedls)} FROM TABLE

For example we call UCASE function using escape sequence {fn .. }:
SELECT {fn UCASE(NAME)} FROM EMP

Which functions supports in my database server?
When this article was published we were implemented 69 ODBC functions in IBProvider. Below they grouped by categories:
String functions.
Function name 
IB 4 
IB 5 
IB 6 
IB 6.5 
IB 7 
IB 7.1 
IB 7.5 
IB 2007 
FB 1 
FB 1.5 
FB 2 
FB 2.1 
YA 
ASCII 











+ 
+ 
BIT_LENGTH 










+ 
+ 

CHAR 











+ 
+ 
CHAR_LENGTH 










+ 
+ 
+ 
CHARACTER_LENGTH 










+ 
+ 
+ 
CONCAT 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
DIFFERENCE 













INSERT 











+ 
+ 
LCASE 










+ 
+ 
+ 
LEFT 








+ 
+ 
+ 
+ 
+ 
LENGTH 










+ 
+ 
+ 
LOCATE 











+ 
+ 
LTRIM 










+ 
+ 
+ 
OCTET_LENGTH 










+ 
+ 

POSITION 











+ 
+ 
REPEAT 











+ 
+ 
REPLACE 











+ 
+ 
RIGHT 










+ 
+ 
+ 
RTRIM 










+ 
+ 
+ 
SOUNDEX 













SPACE 










+ 
+ 
+ 
SUBSTRING 








+ 
+ 
+ 
+ 
+ 
UCASE 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
Numeric functions.
Function name 
IB 4 
IB 5 
IB 6 
IB 6.5 
IB 7 
IB 7.1 
IB 7.5 
IB 2007 
FB 1 
FB 1.5 
FB 2 
FB 2.1 
YA 
ABS 






+ 
+ 

+ 
+ 
+ 
+ 
ACOS 











+ 
+ 
ASIN 











+ 
+ 
ATAN 











+ 
+ 
ATAN2 











+ 
+ 
CEILING 











+ 
+ 
COS 











+ 
+ 
COT 











+ 
+ 
DEGREES 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
EXP 











+ 
+ 
FLOOR 











+ 
+ 
LOG 











+ 
+ 
LOG10 











+ 
+ 
MOD 











+ 
+ 
PI 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
POWER 











+ 
+ 
RADIANS 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
RAND 











+ 
+ 
ROUND 











+ 
+ 
SIGN 






+ 
+ 

+ 
+ 
+ 
+ 
SIN 











+ 
+ 
SQRT 











+ 
+ 
TAN 











+ 
+ 
TRUNCATE 











+ 
+ 
Date and time functions.
Function name 
IB 4 
IB 5 
IB 6 
IB 6.5 
IB 7 
IB 7.1 
IB 7.5 
IB 2007 
FB 1 
FB 1.5 
FB 2 
FB 2.1 
YA 
CURRENT_DATE 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
CURRENT_TIME^{[see below]} 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
CURRENT_TIMESTAMP 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
CURDATE 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
CURTIME 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
DAYNAME 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
DAYOFMONTH 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
DAYOFWEEK 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
DAYOFYEAR 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
EXTRACT 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
HOUR 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
MINUTE 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
MONTH 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
MONTHNAME 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
NOW 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
QUARTER 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
SECOND 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
TIMESTAMPADD 











+ 
+ 
TIMESTAMPDIFF^{[see below]} 











+ 

WEEK 











+ 

YEAR 


+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
NOTE
Function CURRENT_TIME available only for 2 and 3 DBMS dialects.
SQL_TSI_WEEK, SQL_TSI_QUARTER intervals not supported for TIMESTAMPDIFF.

System functions.
Function name 
IB 4 
IB 5 
IB 6 
IB 6.5 
IB 7 
IB 7.1 
IB 7.5 
IB 2007 
FB 1 
FB 1.5 
FB 2 
FB 2.1 
YA 
DATABASE 













IFNULL 






+ 
+ 

+ 
+ 
+ 
+ 
USER 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
Explicit Data Type Conversion Function.
Function name 
IB 4 
IB 5 
IB 6 
IB 6.5 
IB 7 
IB 7.1 
IB 7.5 
IB 2007 
FB 1 
FB 1.5 
FB 2 
FB 2.1 
YA 
CONVERT 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
+ 
NOTE
Following types supported by the CONVERT function: SQL_BIGINT, SQL_CHAR, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_LONGVARCHAR, SQL_NUMERIC, SQL_REAL, SQL_SMALLINT, SQL_DATE, SQL_TIME, SQL_TIMESTAMP, SQL_VARCHAR
Types SQL_BIGINT and SQL_TIME supported only for 2 and 3 dialects.
Type SQL_LONGVARCHAR supported in Firebird 2.1 or greater.

Functions Description and the samples of functions calls.
String functions.
Function 
Description 
Example 
ASCII(string_exp) 
Returns the ASCII code value of the leftmost character of string_exp as an integer. 
{fn ASCII('A')} = 65 {fn ASCII('BCD')} = 66 
BIT_LENGTH(string_exp) 
Returns the length in bits of the string expression. 
{fn BIT_LENGTH('hello')} = 40 {fn BIT_LENGTH('hello')} = 80 for Unicode 
CHAR(code) 
Returns the character that has the ASCII code value specified by code. 
{fn CHAR(65)} = 'A' 
CHAR_LENGTH(string_exp) 
Returns the length in characters of the string expression. This function is the same as the CHARACTER_LENGTH function. 
{fn CHAR_LENGTH('hello')} = 5 
CHARACTER_LENGTH(string_exp) 
Returns the length in characters of the string expression. This function is the same as the CHAR_LENGTH function. 
{fn CHARACTER_LENGTH ('hello')} = 5 
CONCAT(string_exp1, string_exp2) 
Returns a character string that is the result of concatenating string_exp2 to string_exp1. 
{fn CONCAT('Hel', 'lo')} = 'Hello' 
INSERT(string_exp1, start, length, string_exp2) 
Returns a character string where length characters have been deleted from string_exp1, beginning at start, and where string_exp2 has been inserted into string_exp, beginning at start. 
{fn INSERT('1.23.2',3,2,'8')} = '1.8.2' 
LCASE(string_exp) 
Returns a string equal to that in string_exp, with all uppercase characters converted to lowercase. 
{fn LCASE('HELLO')} = ' hello' 
LEFT(string_exp, count) 
Returns the leftmost count characters of string_exp. 
{fn LEFT(' hello', 4)} = ' hell' 
LENGTH (string_exp) 
Returns the number of characters in string_exp, excluding trailing blanks. 
{fn LENGTH(' hello ')} = 5 
LOCATE (string_exp1, string_exp2[, start]) 
Returns the starting position of the first occurrence of string_exp1 within string_exp2.
The search for the first occurrence of string_exp1 begins with the first character position in string_exp2 unless the optional argument, start, is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned. 
{fn LOCATE('ll', 'hello')} = 3 {fn LOCATE('la', 'hello')} = 0 {fn LOCATE('lo', 'hello', 3)} = 4 {fn LOCATE('ll', 'hello', 4)} = 0

LTRIM (string_exp) 
Returns the characters of string_exp, with leading blanks removed. 
{fn LTRIM (' hello ')} = 'hello ' 
OCTET_LENGTH (string_exp) 
Returns the length in bytes of the string expression. 
{fn OCTET_LENGTH('hello')} = 5 
POSITION (string_exp1 IN string_exp2) 
Returns the position of the first character expression in the second character expression. The result is an exact numeric with an implementationdefined precision and a scale of 0. 
{fn POSITION('ll' IN 'hello')} = 3 {fn POSITION('la' IN 'hello')} = 0 
RIGHT (string_exp, count) 
Returns the rightmost count characters of string_exp. 
{fn RIGHT('hello', 2)} = 'lo' 
RTRIM (string_exp) 
Returns the characters of string_exp with trailing blanks removed. 
{fn RTRIM (' hello ')} = ' hello' 
SPACE (count) 
Returns a character string consisting of count spaces. 
{fn SPACE(5)} = ' ' 
SUBSTRING (string_exp, start, length) 
Returns a character string that is derived from string_exp, beginning at the character position specified by start for length characters. 
{fn SUBSTRING('hello', 2, 3)} = 'ell' 
UCASE (string_exp) 
Returns a string equal to that in string_exp, with all lowercase characters converted to uppercase. 
{fn UCASE ('Hello')} = 'HELLO' 
Numeric Functions.
Function 
Description 
Example 
ABS (numeric_exp) 
Returns the absolute value of numeric_exp. 
{fn ABS(5)} = 5 {fn ABS(5)} = 5 
ACOS (float_exp) 
Returns the arccosine of float_exp as an angle, expressed in radians. 
{fn ACOS(1)} = 0 
ASIN (float_exp) 
Returns the arcsine of float_exp as an angle, expressed in radians. 
{fn ASIN(0)} = 1 
ATAN (float_exp) 
Returns the arctangent of float_exp as an angle, expressed in radians. 
{fn ATAN(0)} = 0 
ATAN2 (float_exp1, float_exp2) 
Returns the arctangent of the x and y coordinates, specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians. 
{fn ATAN2(0, 1)} = 0 
CEILING (numeric_exp) 
Returns the smallest integer greater than or equal to numeric_exp. 
{fn CEILING(5.6)} = 6 {fn CEILING(5.6)} = 5 
COS (float_exp) 
Returns the cosine of float_exp, where float_exp is an angle expressed in radians. 
{fn COS(0)} = 1 
COT (float_exp) 
Returns the cotangent of float_exp, where float_exp is an angle expressed in radians. 
{fn COT(1)} = 0.642() 
DEGREES (numeric_exp) 
Returns the number of degrees converted from numeric_exp radians. 
{fn DEGREES(1)} = 57.295() 
EXP (float_exp) 
Returns the exponential value of float_exp. 
{fn EXP(0)} = 1 
FLOOR (numeric_exp) 
Returns the largest integer less than or equal to numeric_exp. 
{fn FLOOR(5.6)} = 5 {fn FLOOR(5.6)} = 6 
LOG (float_exp) 
Returns the natural logarithm of float_exp. 
{fn LOG(1)} = 0 
LOG10 (float_exp) 
Returns the base 10 logarithm of float_exp. 
{fn LOG10(1)} = 0 
MOD (integer_exp1, integer_exp2) 
Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. 
{fn MOD (16, 6)} = 4 
PI ( ) 
Returns the constant value of pi as a floatingpoint value. 
{fn PI()} = 3.1419() 
POWER (numeric_exp, integer_exp) 
Returns the value of numeric_exp to the power of integer_exp. 
{fn POWER(2,3)} = 8 
RADIANS (numeric_exp) 
Returns the number of radians converted from numeric_exp degrees. 
{fn RADIANS(360)} = 6.283 
RAND () 
Returns a random floatingpoint value. 
{fn RAND()} = random value in range [0..1] 
ROUND (numeric_exp, integer_exp) 
Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to integer_exp places to the left of the decimal point. 
{fn ROUND (2.236, 2)} = 2.24 {fn ROUND (125.15, 1)} = 130 
SIGN (numeric_exp) 
Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, 1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned. 
{fn SIGN(15)} = 1 {fn SIGN(1.25)} = 1 {fn SIGN(0)} = 0 
SIN (float_exp) 
Returns the sine of float_exp, where float_exp is an angle expressed in radians. 
{fn SIN(0)} = 0 
SQRT (float_exp) 
Returns the square root of float_exp. 
{fn SQRT(9)} = 3 
TAN (float_exp) 
Returns the tangent of float_exp, where float_exp is an angle expressed in radians. 
{fn TAN(0)} = 0 
TRUNCATE (numeric_exp, integer_exp) 
Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to integer_exp places to the left of the decimal point. 
{fn TRUNCATE (2.236, 2)} = 2.23 {fn TRUNCATE (125.15, 1)} = 120 
Date and time functions.
Function 
Description 
Example 
CURRENT_DATE () 
Returns the current date. 
{fn CURRENT_DATE()} 
CURRENT_TIME 
Returns the current local time. 
{fn CURRENT_TIME} 
CURRENT_TIMESTAMP 
Returns the current local date and local time as a timestamp value. 
{fn CURRENT_TIMESTAMP} 
CURDATE () 
Returns the current date. 
{fn CURDATE()} 
CURTIME () 
Returns the current local time. 
{fn CURTIME()} 
DAYNAME (date_exp) 
Returns a character string containing name of the day. first_week_day = [1 7 ] add the parameter into the Connection String or init them through the ADODB.Command parameters collection (in ADO). Default value is 1 (Monday).
For Interbase >= 7.5, Firebird >= 1.5 and Yaffil function returns actual name of the day:Monday, Tuesday, Wednesday, etc. For old versions function returns symbolic name: WEEKDAY_1  Monday WEEKDAY_2  Tuesday WEEKDAY_3  Wednesday etc. 
For Interbase >= 7.5, Firebird >= 1.5, Yaffil: {fn DAYNAME({d '20071001'})} = 'Monday'
For old versions: first_week_day = 1 [default] {fn MONTHNAME({d '20070401'})} = 'WEEKDAY_1'
first_week_day = 7 {fn MONTHNAME({d '20070401'})} = 'WEEKDAY_2' 
DAYOFMONTH (date_exp) 
Returns the day of the month based on the month field in date_exp as an integer value in the range of 131. 
{fn DAYOFMONTH({d '20070125'})} = 25 
DAYOFWEEK (date_exp) 
Returns the day of the week based on the week field in date_exp as an integer value in the range of 17. Returned value depends on the first_week_day init parameter. See the DAYNAME function description for details. 
first_week_day = 1 [default] {fn DAYOFWEEK({d '20070107'})} = 7
first_week_day = 7 {fn DAYOFWEEK({d '20070107'})} = 1 
DAYOFYEAR (date_exp) 
Returns the day of the year based on the year field in date_exp as an integer value in the range of 1366. 
{fn DAYOFYEAR({d '20070102'})} = 2 {fn DAYOFYEAR({d '20071231'})} = 365 
EXTRACT (extractfield FROM extractsource) 
Returns the extractfield portion of the extractsource. The extractsource argument is a datetime expression. The extractfield argument can be one of the following keywords:
YEAR MONTH DAY HOUR MINUTE SECOND 
{fn EXTRACT(YEAR FROM {d '20000101'})} = 2000 {fn EXTRACT(MONTH FROM {d '20000501'})} = 5 {fn EXTRACT(DAY FROM {d '20070828'})} = 28 {fn EXTRACT(HOUR FROM {ts '20080805 02:02:03'})} = 2 {fn EXTRACT(MINUTE FROM {t '02:10:03'})} = 10 {fn EXTRACT(SECOND FROM {t '01:02:00.589'})} = 0.589

HOUR (time_exp) 
Returns the hour based on the hour field in time_exp as an integer value in the range of 023. 
{fn HOUR({ts '20080508 02:02:03'})} = 2 
MINUTE (time_exp) 
Returns the minute based on the minute field in time_exp as an integer value in the range of 059. 
{fn MINUTE({t '11:59:03'})} = 59 
MONTH (date_exp) 
Returns the month based on the month field in date_exp as an integer value in the range of 112. 
{fn MONTH({d '20000401'})} = 12 
MONTHNAME (date_exp) 
Returns a character string containing name of the month for the month portion of date_exp.For Interbase >= 7.5, Firebird >= 1.5 and Yaffil function returns actual name of the day:January, February, March, etc. For old versions function returns symbolic name: MONTH _1  January MONTH _2  February MONTH _3  March

For Interbase >= 7.5, Firebird >= 1.5, Yaffil: {fn MONTHNAME({d '20070401'})} = 'April'
For old DBMS versions: {fn MONTHNAME({d '20070401'})} = 'MONTH_4' 
NOW () 
Returns current date and time as a timestamp value. 
{fn NOW()} 
QUARTER (date_exp) 
Returns the quarter in date_exp as an integer value in the range of 14, where 1 represents January 1 through March 31. 
{fn QUARTER({d '20070401'})} = 2 
SECOND (time_exp) 
Returns the second based on the second field in time_exp as an integer value in the range of 059. 
{fn SECOND({t '11:00:59'})} = 59 
TIMESTAMPADD (interval, integer_exp, timestamp_exp) 
Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of interval are the following keywords:
SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_WEEK SQL_TSI_MONTH SQL_TSI_QUARTER SQL_TSI_YEAR
where fractional seconds are expressed in billionths of a second. 
{fn TIMESTAMPADD(SQL_TSI_QUARTER, 2, {d '19980502'})} = '19981102' {fn TIMESTAMPADD(SQL_TSI_DAY, 10, {d '20040304'})} = '20040223' {fn TIMESTAMPADD(SQL_TSI_MONTH, 23, {d '19980501'})} = '20000401' {fn TIMESTAMPADD(SQL_TSI_WEEK, 2, {ts '20071018 01:02:03'})} = '20071101 01:02:03' {fn TIMESTAMPADD(SQL_TSI_DAY, 10, {d '20040304'})} = '20040223' {fn TIMESTAMPADD(SQL_TSI_HOUR, 23, {ts '19980501 01:02:03'})} = '19980502 00:02:03' {fn TIMESTAMPADD(SQL_TSI_MINUTE, 63, {t '01:02:03'})} = '23:59:03' {fn TIMESTAMPADD(SQL_TSI_SECOND, 63, {t '01:02:03'})} = '01:01:00' {fn TIMESTAMPADD(SQL_TSI_FRAC_SECOND, 51000000, {t '01:02:03'})} = 01:02:03.0510'

TIMESTAMPDIFF (interval, timestamp_exp1, timestamp_exp2) 
Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid values of interval are the following keywords:
SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_MONTH SQL_TSI_YEAR
where fractional seconds are expressed in billionths of a second. 
{fn TIMESTAMPDIFF(SQL_TSI_YEAR, {ts '19981229 01:02:03'}, {ts '20030101 01:02:03'})} = 5 {fn TIMESTAMPDIFF(SQL_TSI_MONTH, {d '20070130'}, {d '20070131'})} = 0 {fn TIMESTAMPDIFF(SQL_TSI_DAY, {d '20000304'}, {d '20000222'})} = 11 {fn TIMESTAMPDIFF(SQL_TSI_HOUR, {ts '19980501 01:02:03'}, {ts '19980502 00:02:03'})} = 23 {fn TIMESTAMPDIFF(SQL_TSI_MINUTE, {t '01:59:03'}, {t '01:02:05'})} = 57 {fn TIMESTAMPDIFF(SQL_TSI_SECOND, {t '01:02:03'}, {t '01:01:00'})} = 63 {fn TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND, {t '01:02:03.051'}, {t '01:02:03.010'})} = 41000000 
WEEK (date_exp) 
Returns the week of the year based on the week field in date_exp as an integer value in the range of 153. 
{fn WEEK({d '20070101'})} = 0 {fn WEEK({d '20060101'})} = 52 
YEAR (date_exp) 
Returns the year based on the year field in date_exp as an integer value. The range is data sourcedependent. 
{fn YEAR({d '19820619'})} = 1982 
System functions.
Function 
Description 
Example 
IFNULL (exp, value) 
If exp is null, value is returned. If exp is not null, exp is returned. The possible data type or types of value must be compatible with the data type of exp. 
{fn IFNULL(NULL, 'Hello') = 'Hello' {fn IFNULL('Test', 'Hello') = 'Test' 
USER () 
Returns the user name in the DBMS. 
{fn USER()} 
Explicit Data Type Conversion Function.
Function 
Description 
Example 
CONVERT (value_exp, data_type) 
The function returns the value specified by value_exp converted to the specified data_type, where data_type is one of the following keywords: SQL_BIGINT, SQL_CHAR, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_LONGVARCHAR, SQL_NUMERIC, SQL_REAL, SQL_SMALLINT, SQL_DATE, SQL_TIME, SQL_TIMESTAMP, SQL_VARCHAR.
For some types you can specify optional parameters: SQL_CHAR(length), SQL_DECIMAL(precision, scale), SQL_NUMERIC(precision, scale), SQL_VARCHAR(length).

{fn CONVERT(195, SQL_CHAR)} = '195' {fn CONVERT(195, SQL_DECIMAL(5,0))} = 195 {fn CONVERT('15.81', SQL_FLOAT)} = 15.81 {fn CONVERT('01.02.2007', SQL_DATE)} = 01.02.2007 {fn CONVERT(195, SQL_VARCHAR(10))} = '195'

Useful links.


Connect with Us