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

{fnfunctionCall}

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.

Note: Any Derby built-in function is
allowed in this syntax, not just those listed in this section.

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.

- 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)}

Returns a timestamp value one month later than the current timestamp.

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

Returns the number of weeks between now and the specified time on January 1, 2001.