JDBC escape syntax for fn keyword

Syntax { fn functionCall }

where functionCall is the name of one of the scalar functions listed below. The functions are of the following types:

Numeric functions
String functions
Date and time functions
System function

Numeric functions

abs: Returns the absolute value of a number. abs ( numericExpression )
The JDBC escape syntax {fn abs(numericExpression)} is equivalent to the built-in syntax ABS(numericExpression). For more information, see .
acos: Returns the arc cosine of a specified number. acos ( number )
The JDBC escape syntax {fn acos(number)} is equivalent to the built-in syntax ACOS(number). For more information, see .
asin: Returns the arc sine of a specified number. asin ( number )
The JDBC escape syntax {fn asin(number)} is equivalent to the built-in syntax ASIN(number). For more information, see .
atan: Returns the arc tangent of a specified number. atan ( number )
The JDBC escape syntax {fn atan(number)} is equivalent to the built-in syntax ATAN(number). For more information, see .
atan2: Returns the arc tangent in radians of y/x. atan2 ( y, x )
The JDBC escape syntax {fn atan2(y, x)} is equivalent to the built-in syntax ATAN2(y, x). For more information, see .
ceiling: Rounds the specified number up, and returns the smallest number that is greater than or equal to the specified number. ceiling ( number )
The JDBC escape syntax {fn ceiling(number)} is equivalent to the built-in syntax CEILING(number). For more information, see .
cos: Returns the cosine of a specified number. cos ( number )
The JDBC escape syntax {fn cos(number)} is equivalent to the built-in syntax COS(number). For more information, see .
cot: Returns the cotangent of a specified number. cot ( number )
The JDBC escape syntax {fn cot(number)} is equivalent to the built-in syntax COT(number). For more information, see .
degrees: Converts a specified number from radians to degrees. degrees ( number )
The JDBC escape syntax {fn degrees(number)} is equivalent to the built-in syntax DEGREES(number). For more information, see .
exp: Returns e raised to the power of the specified number. exp ( number )
The JDBC escape syntax {fn exp(number)} is equivalent to the built-in syntax EXP(number). For more information, see .
floor: Rounds the specified number down, and returns the largest number that is less than or equal to the specified number. floor ( number )
The JDBC escape syntax {fn floor(number)} is equivalent to the built-in syntax FLOOR(number). For more information, see .
log: Returns the natural logarithm (base e) of the specified number. log ( number )
The JDBC escape syntax {fn log(number)} is equivalent to the built-in syntax LOG(number). For more information, see .
log10: Returns the base-10 logarithm of the specified number. log10 ( number )
The JDBC escape syntax {fn log10(number)} is equivalent to the built-in syntax LOG10(number). For more information, see .
mod: Returns the remainder (modulus) of argument 1 divided by argument 2. The result is negative only if argument 1 is negative. mod ( integerExpression, integerExpression )
The JDBC escape syntax {fn mod(integerExpression, integerExpression)} is equivalent to the built-in syntax MOD(integerExpression, integerExpression). For more information, see .
pi: Returns a value that is closer than any other value to pi. pi ( )
The JDBC escape syntax {fn pi()} is equivalent to the built-in syntax PI(). For more information, see .
radians: Converts a specified number from degrees to radians. radians ( number )
The JDBC escape syntax {fn radians(number)} is equivalent to the built-in syntax RADIANS(number). For more information, see .
rand: Returns a random number given a seed number. rand ( seed )
The JDBC escape syntax {fn rand(seed)} is equivalent to the built-in syntax RAND(seed). For more information, see .
sign: Returns an integer that represents the sign of a specified number (+1 if the number is positive, -1 if it is negative, 0 if it is 0). sign ( number )
The JDBC escape syntax {fn sign(number)} is equivalent to the built-in syntax SIGN(number). For more information, see .
sin: Returns the sine of a specified number. sin ( number )
The JDBC escape syntax {fn sin(number)} is equivalent to the built-in syntax SIN(number). For more information, see .
sqrt: Returns the square root of a floating-point number. sqrt ( number )
The JDBC escape syntax {fn sqrt(number)} is equivalent to the built-in syntax SQRT(number). For more information, see .
tan: Returns the tangent of a specified number. tan ( number )
The JDBC escape syntax {fn tan(number)} is equivalent to the built-in syntax TAN(number). For more information, see .

String functions

concat: Returns the concatenation of character strings; that is, the character string formed by appending the second string to the first string. If either string is null, the result is NULL. concat ( characterExpression, characterExpression )
The JDBC escape syntax {fn concat(characterExpression, characterExpression)} is equivalent to the built-in syntax characterExpression || characterExpression. For more information, see .
lcase: Returns a string in which all alphabetic characters in the argument have been converted to lowercase. lcase ( characterExpression )
The JDBC escape syntax {fn lcase(characterExpression)} is equivalent to the built-in syntax LCASE(characterExpression). For more information, see .
length: Returns the number of characters in a character string expression. length ( characterExpression )
The JDBC escape syntax {fn length(characterExpression)} is equivalent to the built-in syntax LENGTH(characterExpression). For more information, see .
locate: Returns the position in the second characterExpression of the first occurrence of the first characterExpression. Searches from the beginning of the second characterExpression, unless the startIndex parameter is specified. locate ( characterExpression, characterExpression [ , startIndex ] )
The JDBC escape syntax {fn locate(characterExpression, characterExpression [, startIndex] )} is equivalent to the built-in syntax LOCATE(characterExpression, characterExpression [, startPosition] ). For more information, see .
ltrim: Removes blanks from the beginning of a character string expression. ltrim ( characterExpression )
The JDBC escape syntax {fn ltrim(characterExpression)} is equivalent to the built-in syntax LTRIM(characterExpression). For more information, see .
rtrim: Removes blanks from the end of a character string expression. rtrim ( characterExpression )
The JDBC escape syntax {fn rtrim(characterExpression)} is equivalent to the built-in syntax RTRIM(characterExpression). For more information, see .
substring: Forms a character string by extracting length characters from the characterExpression beginning at startIndex. The index of the first character in the characterExpression is 1. substring ( characterExpression, startIndex, length )
The JDBC escape syntax {fn substring(characterExpression, startIndex, length)} is equivalent to the built-in syntax SUBSTR(characterExpression, startIndex, length). For more information, see .
ucase: Returns a string in which all alphabetic characters in the argument have been converted to uppercase. ucase ( characterExpression )
The JDBC escape syntax {fn ucase(characterExpression)} is equivalent to the built-in syntax UCASE(characterExpression). For more information, see .

Date and time functions

curdate: Returns the current date. curdate ( )
The JDBC escape syntax {fn curdate()} is equivalent to the built-in syntax CURRENT_DATE. For more information, see .
curtime: Returns the current time. curtime ( )
The JDBC escape syntax {fn curtime()} is equivalent to the built-in syntax CURRENT_TIME. For more information, see .
hour: Returns the hour part of a time value. hour ( expression )
The JDBC escape syntax {fn hour(expression)} is equivalent to the built-in syntax HOUR(expression). For more information, see .
minute: Returns the minute part of a time value. minute ( expression )
The JDBC escape syntax {fn minute(expression)} is equivalent to the built-in syntax MINUTE(expression). For more information, see .
month: Returns the month part of a date value. month ( expression )
The JDBC escape syntax {fn month(expression)} is equivalent to the built-in syntax MONTH(expression). For more information, see .
second: Returns the seconds part of a time value. second ( expression )
The JDBC escape syntax {fn second(expression)} is equivalent to the built-in syntax SECOND(expression). For more information, see .
TIMESTAMPADD: 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.
TIMESTAMPADD is a JDBC escaped function and is accessible only by using the JDBC escape function syntax.
TIMESTAMPADD ( interval, integerExpression, timestampExpression )
To perform TIMESTAMPADD on dates and times, it is necessary to convert the dates and times 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.

Do 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: 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.
The TIMESTAMPDIFF is a JDBC escaped function and is accessible only by using the JDBC escape function syntax.
TIMESTAMPDIFF ( interval, timestampExpression1, timestampExpression2 )
To perform TIMESTAMPDIFF on dates and times, it is necessary to convert the dates and times 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.

Do not put a datetime column inside a timestamp arithmetic function in WHERE clauses, because the optimizer will not use any index on the column.
year: Returns the year part of a date value. year ( expression )
The JDBC escape syntax {fn year(expression)} is equivalent to the built-in syntax YEAR(expression). For more information, see .

Valid intervals for TIMESTAMPADD and TIMESTAMPDIFF The TIMESTAMPADD and TIMESTAMPDIFF functions are 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

Examples for the TIMESTAMPADD and TIMESTAMPDIFF escape functions

To return a timestamp value one month later than the current timestamp, use the following syntax:

{fn TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP)}

To return the number of weeks between now and the specified time on January 1, 2008, use the following syntax:

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

System function

user: Returns the authorization identifier or name of the current user. If there is no current user, it returns APP. user ( )
The JDBC escape syntax {fn user()} is equivalent to the built-in syntax USER. For more information, see .