JDBC escape syntax for fn keyword

JDBC escape syntax for fn keyword fnJDBC escape keywordJDBC escape keywordescape keywordJDBC JDBCescape functionssubstringJDBC scalar functionmodJDBC scalar functionlocateJDBC scalar function absJDBC scalar functionsqrtJDBC scalar functionconcatJDBC scalar functionTIMESTAMPADD functionJDBC scalar functionTIMESTAMPDIFF functionJDBC scalar functionacosJDBC scalar functionasinJDBC scalar function atanJDBC scalar functionceilingJDBC scalar functioncosJDBC scalar functiondegreesJDBC scalar function expJDBC scalar functionfloorJDBC scalar functionlogJDBC scalar functionlog10JDBC scalar functionpiJDBC scalar functionradiansJDBC scalar functionsinJDBC scalar functiontanJDBC scalar function

You can specify functions in JDBC escape syntax, by using the fn keyword.

Syntax{fn functionCall}

where functionCall is the name of one of the following scalar 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 ABSOLUTE(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 .
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 .
concat: Returns the concatenation of character strings.concat(CharacterExpression, CharacterExpression)
Character string formed by appending the second string to the first string. If either string is null, the result is NULL. The JDBC escape syntax {fn concat (CharacterExpression, CharacterExpression) is equivalent to the built-in syntax { CharacterExpression || CharacterExpression }. 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 .
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 .
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 .
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(integer_type, integer_type)
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 .
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 floating point number.sqrt(FloatingPointExpression)
The JDBC escape syntax {fn sqrt (FloatingPointExpression)} is equivalent to the built-in syntax SQRT(FloatingPointExpression). 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)
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 .
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.
The TIMESTAMPADD is a JDBC escaped function, and is only accessible 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.
You should not put a datetime column inside of 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 only accessible 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.
You should not put a datetime column inside of a timestamp arithmetic function in WHERE clauses because the optimizer will not use any index on the column.

Valid intervals for TIMESTAMPADD and TIMESTAMPDIFFThe 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'))}