__2007-12-04__

__2015-08-19__

# The Compete Guide to ODBC Escape Sequences in Firebird and InterBase.

## What are ODBC Escape Sequences?

ODBC Escape sequences has 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 let’s 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 let’s 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 ‘yyyy-mm-dd’} |

Time | {t ‘hh:mm:ss’} |

Timestamp | {ts ‘yyyy-mm-dd hh:mm:ss’} |

Sequence {t …}

Example of converting date string via escape sequence:

SELECT COUNT(id) AS cnt FROM employee WHERE hire_date > {d ‘2007-01-01’} |

**NOTE**

In the example string contains date in the escape sequence format {d ‘

**yyyy-mm-dd’**}, 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 procedure-name[([parameter][,[parameter]]…)]} |

It the following example SP contains 1 input and 4 output parameters:

{ |

## 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_BIT, 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. - Type
**SQL_BIT**supported in Firebird 2.1+ and in InterBase 7+.

## 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 implementation-defined 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 floating-point 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 floating-point 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 ‘2007-10-01’})} = ‘Monday’ For old versions: first_week_day = 1 [default] {fn DAYNAME({d ‘2007-04-01’})} = ‘WEEKDAY_1’ first_week_day = 7 {fn DAYNAME({d ‘2007-04-01’})} = ‘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 1-31. |
{fn DAYOFMONTH({d ‘2007-01-25’})} = 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 1-7. 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 ‘2007-01-07’})} = 7 first_week_day = 7 {fn DAYOFWEEK({d ‘2007-01-07’})} = 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 1-366. |
{fn DAYOFYEAR({d ‘2007-01-02’})} = 2 {fn DAYOFYEAR({d ‘2007-12-31’})} = 365 |

EXTRACT (extract-field FROM extract-source) |
Returns the extract-field portion of the extract-source. The extract-source argument is a datetime expression. The extract-field argument can be one of the following keywords:YEAR MONTH DAY HOUR MINUTE SECOND |
{fn EXTRACT(YEAR FROM {d ‘2000-01-01’})} = 2000 {fn EXTRACT(MONTH FROM {d ‘2000-05-01’})} = 5 {fn EXTRACT(DAY FROM {d ‘2007-08-28’})} = 28 {fn EXTRACT(HOUR FROM {ts ‘2008-08-05 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 0-23. |
{fn HOUR({ts ‘2008-05-08 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 0-59. |
{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 1-12. |
{fn MONTH({d ‘2000-04-01’})} = 4 |

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 ‘2007-04-01’})} = ‘April’ For old DBMS versions: {fn MONTHNAME({d ‘2007-04-01’})} = ‘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 1-4, where 1 represents January 1 through March 31. | {fn QUARTER({d ‘2007-04-01’})} = 2 |

SECOND (time_exp) |
Returns the second based on the second field in time_exp as an integer value in the range of 0-59. | {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 ‘1998-05-02’})} = ‘1998-11-02’ {fn TIMESTAMPADD(SQL_TSI_DAY, -10, {d ‘2004-03-04’})} = ‘2004-02-23’ {fn TIMESTAMPADD(SQL_TSI_MONTH, 23, {d ‘1998-05-01’})} = ‘2000-04-01’ {fn TIMESTAMPADD(SQL_TSI_WEEK, 2, {ts ‘2007-10-18 01:02:03’})} = ‘2007-11-01 01:02:03’ {fn TIMESTAMPADD(SQL_TSI_DAY, -10, {d ‘2004-03-04’})} = ‘2004-02-23’ {fn TIMESTAMPADD(SQL_TSI_HOUR, 23, {ts ‘1998-05-01 01:02:03’})} = ‘1998-05-02 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 ‘1998-12-29 01:02:03’}, {ts ‘2003-01-01 01:02:03’})} = 5 {fn TIMESTAMPDIFF(SQL_TSI_MONTH, {d ‘2007-01-30’}, {d ‘2007-01-31’})} = 0 {fn TIMESTAMPDIFF(SQL_TSI_DAY, {d ‘2000-03-04’}, {d ‘2000-02-22’})} = 11 {fn TIMESTAMPDIFF(SQL_TSI_HOUR, {ts ‘1998-05-01 01:02:03’}, {ts ‘1998-05-02 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 1-53. | {fn WEEK({d ‘2007-01-01’})} = 0 {fn WEEK({d ‘2006-01-01’})} = 52 |

YEAR (date_exp) |
Returns the year based on the year field in date_exp as an integer value. The range is data source-dependent. |
{fn YEAR({d ‘1982-06-19’})} = 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_BIT, 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.

- Download Firebird
- ODBC Firebird driver, ODBC InterBase or OLE DB?
- Olivier Bridgeman
ODBC Escape Sequences - IBProvider. Samples of calling SP using ODBC escape sequence How to connect Firebird/InterBase database to MS SQL Linked Server
- IBProvider. Samples of calling SP using ODBC escape sequence (IN, OUT parameters) (C++, VB)
- IBProvider. Samples of calling SP using ODBC escape sequence (Returning rowset) (C++, VB)
- MSDN article
ODBC Escape Sequences - MSDN article
Escape Sequences in ODBC - Connect to Firebird/InterBase in .Net (c# examples)
- Using new features of Firebird 2.1 in the ADO.Net (c#).
- Using new features of Firebird 2.0 in the ADO.Net (c#).

__2007-12-04__. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link http://www.ibprovider.com/eng required.