JDBC escape syntax for fn keyword

The fn keyword allows the use of several scalar functions. The function name follows the keyword fn.

Syntax

{fn functionCall}

where functionCall is one of the following functions:

concat (CharacterExpression, CharacterExpression)

Character string formed by appending the second string to the first; if either string is null, the result is NULL. {fn concat (CharacterExpression, CharacterExpression) is equivalent to built-in syntax { CharacterExpression || CharacterExpression }. For more details, see Concatenation.

sqrt (FloatingPointExpression)

Square root of floating point number.

{fn sqrt (FloatingPointExpression)} is equivalent to built-in syntax SQRT(FloatingPointExpression). For more details see SQRT.

abs (NumericExpression)

Absolute value of number. {fn abs(NumericExpression)} is equivalent to built-in syntax ABSOLUTE(NumericExpression). For more details see ABS or ABSVAL.

locate(CharacterExpression,CharacterExpression [, startIndex] )

Position in the second CharacterExpression of the first occurrence of the first CharacterExpression, searching from the beginning of the second character expression, unless startIndex is specified. {fn locate(CharacterExpression,CharacterExpression [, startIndex] )} is equivalent to the built-in syntax LOCATE(CharacterExpression, CharacterExpression [, StartPosition] ). For more details see LOCATE.

substring(CharacterExpression, startIndex, length)

A character string formed by extracting length characters from the CharacterExpression beginning at startIndex; the index starts with 1.

mod(integer_type, integer_type)

MOD returns the remainder (modulus) of argument 1 divided by argument 2. The result is negative only if argument 1 is negative. For more details, see MOD.

TIMESTAMPADD( interval, integerExpression, timestampExpression )

Use the TIMESTAMPADD function to add the value of an interval to a timestamp. The function applies the integer to the specified timestamp based on the interval type and returns the sum as a new timestamp. You can subtract from the timestamp by using negative integers.

Note that TIMESTAMPADD is a JDBC escaped function, and is only accessible using the JDBC escape function syntax.

To perform TIMESTAMPADD on dates and times, it is necessary to convert them to timestamps. Dates are converted to timestamps by putting 00:00:00.0 in the time-of-day fields. Times are converted to timestamps by putting the current date in the date fields.

Note that you should not put a datetime column inside a timestamp arithmetic function in WHERE clauses because the optimizer will not use any index on the column.

TIMESTAMPDIFF( interval, timestampExpression1, timestampExpression2 )

Use the TIMESTAMPDIFF function to find the difference between two timestamp values at a specified interval. For example, the function can return the number of minutes between two specified timestamps.

Note that TIMESTAMPDIFF is a JDBC escaped function, and is only accessible using the JDBC escape function syntax.

To perform TIMESTAMPDIFF on dates and times, it is necessary to convert them to timestamps. Dates are converted to timestamps by putting 00:00:00.0 in the time-of-day fields. Times are converted to timestamps by putting the current date in the date fields.

Note that you should not put a datetime column inside a timestamp arithmetic function in WHERE clauses because the optimizer will not use any index on the column.

Valid intervals for TIMESTAMPADD and TIMESTAMPDIFF

The TIMESTAMPADD and TIMESTAMPDIFF functions can be used to perform arithmetic with timestamps. These two functions use the following valid intervals for arithmetic operations:

• SQL_TSI_DAY
• SQL_TSI_FRAC_SECOND
• SQL_TSI_HOUR
• SQL_TSI_MINUTE
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_SECOND
• SQL_TSI_WEEK
• SQL_TSI_YEAR

{fn TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP)}

{fn TIMESTAMPDIFF(SQL_TSI_WEEK, CURRENT_TIMESTAMP, 
  timestamp('2001-01-01-12.00.00.000000'))}