# JDBC escape syntax for fn keyword

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 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 ABS or ABSVAL function.

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 ACOS function.

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 ASIN function.

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 ATAN function.

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 ATAN2 function.

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 CEIL or CEILING function.

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 COS function.

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 COT function.

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 DEGREES function.

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 EXP function.

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 FLOOR function.

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 LN or LOG function.

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 LOG10 function.

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 MOD function.

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 PI function.

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 RADIANS function.

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 RAND function.

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 SIGN function.

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 SIN function.

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 SQRT function.

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 TAN function.

## 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 Concatenation operator.

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 LCASE or LOWER function.

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 LENGTH function.

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 LOCATE function.

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 LTRIM function.

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 RTRIM function.

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 SUBSTR function.

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 UCASE or UPPER function.

## 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 CURRENT_DATE function.

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 CURRENT_TIME function.

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 HOUR function.

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 MINUTE function.

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 MONTH function.

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 SECOND function.

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 YEAR function.

## 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 USER function.

Parent topic: JDBC escape syntax
Related reference
JDBC escape keyword for call statements
JDBC escape syntax for LIKE clauses
JDBC escape syntax for limit/offset clauses
JDBC escape syntax for outer joins
JDBC escape syntax for time formats
JDBC escape syntax for date formats
JDBC escape syntax for timestamp formats