2.16.2 System-defined scalar functions
(1) ACOS
(a) Function
Returns in the range 0 to
the angle (in radians) that is the inverse cosine of an argument.
(b) Format
[MASTER.]ACOS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- Specifying in the argument a value whose function value is not defined causes a definition area error (domain error occurs).
(2) ADD_INTERVAL
(a) Function
Returns the predefined character string representation of a time stamp, which is obtained by adding the datetime interval (
YYYYMMDDhhmmss.) in decimal representation specified in argument-2 to the time stamp in predefined character string representation ('YYYY-MM-DD hh:mm:ss') specified in argument-1.
(b) Format
[MASTER.]ADD_INTERVAL(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- Decimal or integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a character string data type (CHAR or VARCHAR), and its length must not exceed 19 bytes.
- If value-expression-1 is a character string data type (CHAR, VARCHAR), a character set other than UTF-16 must be used for the data type. If you specify a character string data type that uses the UTF-16 character set in value-expression-1, use the CAST specification to convert the data type to the character string data type of the default character set.
- The data type of argument-2 must be DECIMAL, INTEGER, or SMALLINT. An integer value must not exceed 14 digits (the decimal point is ignored if specified).
- The data type of the result will be CHAR(19).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The function result uses the character set of argument-1.
- As the value of argument-1, specify the predefined character string representation ('YYYY-MM-DD hh:mm:ss') of a valid time stamp. A fractional second must not be used in the predefined character string representation of a time stamp.
- argument-2 must specify in the format
YYYYMMDDhhmmss. the decimal representation of a date and time interval:
- YYYY: Years
- MM: Months
- DD: Days
- hh: Hours
- mi: Minutes
- ss: Seconds
If the value of argument-2 is positive, the operation adds a datetime interval (YYYYMMDDhhmmss.) to the time stamp specified in argument-1. If the value of argument-2 is negative, the operation results in the subtraction of the datetime interval (YYYYMMDDhhmmss.) from the time stamp specified in argument-1.
- The date and time interval is added or subtracted in the following order: years, months, days, hours, minutes, seconds. If the result falls on a non-existent date (the 31st of a month with fewer than 31 days or on February 29 of a non-leap year), the result is adjusted to the last day of the month.
Adding months to the last day of a month does not necessarily produce the last day of the result month. Also, adding a number of months to a date and subtracting the same number of months from the resulting date does not necessarily produce the original date.
Note that subtracting the same number of months does not necessarily produce the original date.
- If a leap second is specified when the time stamp is represented as a character string in the default format, results are calculated as follows:
- If the result is 60 or more seconds in an operation that manipulates minutes, the result is changed to 59 seconds.
- In an operation that manipulates seconds, 60 seconds is treated as 1 minute and 61 seconds is treated as 1 minute and 1 second.
- A leap second is not included in the function result.
- If the result of the ADD_INTERVAL function is not within the range 0001/01/01 00:00:00 - 9999/12/31 23:59:59, an overflow error occurs. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- The result of the function is the predefined character string representation of a time stamp.
(d) Examples
Examples of the ADD_INTERVAL function are shown below:
ADD_INTERVAL('1999-12-31 23:59:59',10000000001.)
==> '2001-01-01 00:00:00'
ADD_INTERVAL(2001-01-01 00:00:00',-10000000001.)
==> '1999-12-31 23:59:59'
ADD_INTERVAL('1956-06-07 03:15:30',400313115450.)
==> '1996-09-20 15:10:20'
ADD_INTERVAL('1998-12-31 13:59:59',10200030405.)
==> '2000-02-29 17:04:04'
ADD_INTERVAL('2000-02-29 17:04:04',-10200030405.)
==> '1998-12-28 13:59:59'
ADD_INTERVAL('2000-03-05 17:04:60',100.)
==> '2000-03-05 17:05:59'
(If set pd_leap_second = Y is specified in system common definition pdsys)
ADD_INTERVAL('2000-03-05 17:04:60',-60.)
==> '2000-03-05 17:04:00'
(If set pd_leap_second = Y is specified in system common definition pdsys)
ADD_INTERVAL(CAST(CURRENT_TIMESTAMP AS CHAR(19)),-100000000.)
==> '2008-09-29 10:17:48'
(If CURRENT TIMESTAMP is 2008 October, 29, 10:17:48)
(3) ASCII
(a) Function
Returns the ASCII integer value for the first character in the character string specified in an argument.
(b) Format
[MASTER.]ASCII(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR).
- The character set used for the data type of the argument must use the default character set.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the length of the argument value is 0 bytes, the result will be the null value.
(d) Example
An example of the ASCII function is shown below:
ASCII('ABC') ==> 65
CHR(ASCII('ABC')) ==> 'A'
(4) ASIN
(a) Function
Returns in the range -
/2 to
/2 the angle (in radians) that is the inverse sine of an argument.
(b) Format
[MASTER.]ASIN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- Specifying in the argument a value whose function value is not defined causes a definition area error (domain error occurs).
(5) ATAN
(a) Function
Returns in the range -
/2 to
/2 the angle (in radians) that is the inverse tangent of an argument.
(b) Format
[MASTER.]ATAN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(6) ATAN2
(a) Function
Returns in the range -
to
the angle (in radians) that is the inverse tangent of x/y, where x is argument-1 and y is argument-2.
(b) Format
[MASTER.]ATAN2(argument-1, argument-2)
(c) Rules
- The following items can be specified in argument-1 and argument-2:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(7) CEIL
(a) Function
Returns the smallest integer that is greater than or equal to the value of an argument.
(b) Format
[MASTER.]CEIL(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The following table lists the data types of the results.
Table 2-48 Data type of the result of a CEIL system-defined scalar function
Data type of argument | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the CEIL function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(8) CENTURY
(a) Function
Returns as an ordinal number the century of the date specified in an argument.
(b) Format
[MASTER.]CENTURY(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the CENTURY function are shown below:
CENTURY(DATE'1900-12-31')) ==> 19
CENTURY(DATE'1901-01-01')) ==> 20
CENTURY(DATE'1999-12-31')) ==> 20
CENTURY(DATE'2000-12-31')) ==> 20
CENTURY(DATE'2001-01-01')) ==> 21
(9) CHR
(a) Function
Returns the ASCII character represented by the integer value specified in an argument (returns the null value if the argument value is not in the range 0 to 255).
(b) Format
[MASTER.]CHR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be INTEGER or SMALLINT.
- The data type of the result is CHAR(1).
- The character set of the result is the default character set.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Example
An example of the CHR function is shown below:
CHR(65 ==> 'A'
ASCII(CHR(65)) ==> 65
(10) COS
(a) Function
Returns the cosine (COS trigonometric function) of an argument that is an angle specified in radians.
(b) Format
[MASTER.]COS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(11) COSH
(a) Function
Returns the hyperbolic cosine of an argument.
(b) Format
[MASTER.]COSH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the COSH function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(12) DATE_TIME
(a) Function
Returns the date of the DATE type specified in argument-1 and the time of the TIME type specified in argument-2 by converting them into the predefined character string representation ('YYYY-MM-DD hh:mm:ss') of a time stamp.
(b) Format
[MASTER.]DATE_TIME(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- CURRENT_TIME
- Column specification
- SQL variable or SQL parameter
- Time operations
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data type of argument-2 must be TIME.
- The data type of the result is CHAR(19).
- The character set of the result is the default character set.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The predefined character string representation of the time stamp, which is the result of DATE_TIME(date, time), is a value that is obtained by concatenation of the predefined character string representation of date, one space character, and the predefined character string representation of time.
CHAR(date) | | ' ' | | CHAR(time)
(d) Examples
Examples of the DATE_TIME function are shown below:
DATE_TIME(DATE('1999-12-31'), TIME('23:59:59'))
==> '1999-12-31 23:59:59'
DATE_TIME(CURRENT_DATE, CURRENT_TIME)
==> '1999-07-27 11:05:20'
(13) DAYNAME
(a) Function
Returns the character string (such as "Sunday" or "Monday") for the day of the week of the date specified in an argument.
(b) Format
[MASTER.]DAYNAME(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result is VARCHAR(18).
- The character set of the result is the default character set.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the DAYNAME function are shown below:
DAYNAME(DATE('1999-06-06')) ==> 'Sunday'
DAYNAME(DATE('1999-06-07')) ==> 'Monday'
DAYNAME(DATE('1999-06-08')) ==> 'Tuesday'
DAYNAME(DATE('1999-06-09')) ==> 'Wednesday'
DAYNAME(DATE('1999-06-10')) ==> 'Thursday'
DAYNAME(DATE('1999-06-11')) ==> 'Friday'
DAYNAME(DATE('1999-06-12')) ==> 'Saturday'
(14) DAYOFWEEK
(a) Function
Returns the integer indicating the day of the week (1 for Sunday, 2 for Monday, and so on) for the date specified in an argument.
(b) Format
[MASTER.]DAYOFWEEK(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the DAYOFWEEK function are shown below:
DAYOFWEEK(DATE('1999-06-06')) ==> 1
DAYOFWEEK(DATE('1999-06-07')) ==> 2
DAYOFWEEK(DATE('1999-06-08')) ==> 3
DAYOFWEEK(DATE('1999-06-11')) ==> 6
DAYOFWEEK(DATE('1999-06-12')) ==> 7
'1999-06-06' was a Sunday.
(15) DAYOFYEAR
(a) Function
Returns the integer (in the range 1 to 366) that represents the date specified in an argument as the number of days elapsed since January 1.
(b) Format
[MASTER.]DAYOFYEAR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the DAYOFYEAR function are shown below:
DAYOFYEAR(DATE('1999-01-01')) ==> 1
DAYOFYEAR(DATE('1999-02-28')) ==> 53
DAYOFYEAR(DATE('1999-06-07')) ==> 158
DAYOFYEAR(DATE('1999-12-31')) ==> 365
DAYOFYEAR(DATE('2000-12-31')) ==> 366
(16) DEGREES
(a) Function
Converts to degrees the angle specified in radians in an argument.
(b) Format
[MASTER.]DEGREES(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the DEGREES function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(17) EXP
(a) Function
Determines an exponent to the base of the natural logarithm.
(b) Format
[MASTER.]EXP(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the EXP function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(18) FLOOR
(a) Function
Returns the largest integer that is less than or equal to the value of an argument.
(b) Format
[MASTER.]FLOOR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The following table lists the data types of the results.
Table 2-49 Data type of the result of a FLOOR system-defined scalar function
Data type of argument | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the FLOOR function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(19) GREATEST
(a) Function
Returns the largest value among specified arguments.
(b) Format
[MASTER.]GREATEST(argument, argument[, argument])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- A maximum of three arguments can be specified.
- The following can be specified in each value expression:
- Literal
- USER, CURRENT_DATE, or CURRENT_TIME
- Column specification
- SQL variable or SQL parameter
- Arithmetic, date, time, or concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- All specified arguments must be of one of the following data types: numeric, character string, mixed character string, national character string, date, or time.
- If the value expression specified in an argument is a character string data type, use the same character set for all value expressions.
- The following table lists the data types of the results.
Table 2-50 Data type of the result of a GREATEST system-defined scalar function
Data type of integer | Data type of result |
---|
INTEGER or SMALLINT | INTEGER |
DECIMAL [, INTEGER or SMALLINT] | DECIMAL#1 |
FLOAT, SMALLFLT [, DECIMAL, INTEGER, or SMALLINT] | FLOAT |
CHAR or VARCHAR | VARCHAR#2 |
MCHAR or MVARCHAR | MVARCHAR#2 |
NCHAR or NVARCHAR | NVARCHAR#2 |
DATE | DATE |
TIME | TIME |
- #1: The following precision and scaling apply, where pi and si denote the precision and scaling of the ith argument, respectively:
- Precision = max(p1-s1, p2-s2, ...) + max(s1, s2, ...)
- Scaling = max(s1, s2, ...)
- A result whose precision exceeds 38 causes an error.
- INTEGER is treated as DECIMAL(10,0); SMALLINT is treated as DECIMAL(5,0).
- #2: The following maximum length applies, where ni denotes the maximum length of the ith argument (the defined length for a fixed-length data type):
- Maximum length = max(n1, n2, ...)
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- If the data type of the result is a character string data type, the character set used for the result is the character set used for the value expression specified in the argument.
(20) HALF
(a) Function
Based on a month specified in argument-2 and a day specified in argument-3 as the beginning of the fiscal year, returns an integer (1 or 2) indicating whether the date specified in argument-1 is in the first half or the second half of the fiscal year.
(b) Format
[MASTER.]HALF(argument-1 [, argument-2 [, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- For argument-2, the default is 1 and the range of specifiable values is 1 to 12. For argument-3, the default is 1 and the range of specifiable values is 1 to 29 when the value of argument-2 is 2 and 1 to (number of days in the month specified in argument-2) in all other cases.
- The result is determined according to the following rules:
- If day of argument-1-date < argument-3-day:
Number of months = month of argument-1 date - argument-2-month - 1
- If day of argument-1-date
argument-3-day:
Number of months = month of argument-1 date - argument-2-month
- If number of months < 0:
Result = (number of months + 12)
2 + 1
- If number of months
0:
Result = number of months
2 + 1
(d) Examples
Examples of the HALF function are shown below:
HALF(DATE('1999-01-01')) ==> 1
HALF(DATE('1999-09-10')) ==> 2
HALF(DATE('1999-12-31')) ==> 2
HALF(DATE('1999-04-01'), 4) ==> 1
HALF(DATE('1999-09-10'), 4) ==> 1
HALF(DATE('1999-03-31'), 4) ==> 2
HALF(DATE('1999-03-21'), 3, 21) ==> 1
HALF(DATE('1999-09-20'), 3, 21) ==> 1
HALF(DATE('1999-03-20'), 3, 21) ==> 2
(21) INSERTSTR (INSERTSTR_LONG)
(a) Function
Deletes from the character string specified in argument-1 the substring beginning at the character position specified in argument-2 and consisting of the number of characters specified in argument-3, then sets the character string specified in argument-4 at the argument-2 position and returns the modified character string.
(b) Format
[MASTER.]INSERTSTR(argument-1, argument-2, argument-3, argument-4)
[MASTER.]INSERTSTR_LONG(argument-1, argument-2, argument-3, argument-4)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expressions of argument-1 and argument-4:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-4 must both be the same character string data type (CHAR or VARCHAR), mixed character string data type (MCHAR or MVARCHAR), or national character string data type (NCHAR or NVARCHAR).
- If a character string type (CHAR or VARCHAR) value expression is specified in both argument-1 and argument-4, the same character set must be used for both arguments.
- The following table indicates the maximum length of argument-4.
Table 2-51 Maximum length of argument-4 of the INSERTSTR system-defined scalar function
Data type of argument-1 (and argument-4) | Maximum length of argument-4 |
---|
CHAR or VARCHAR | Default character set or a character set other than UTF-16 | 255 |
UTF-16 character set | 510 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
Table 2-52 Maximum length of argument-4 of the INSERTSTR_LONG system-defined scalar function
Data type of argument-1 (and argument-4) | Maximum length of argument-4 |
---|
CHAR or VARCHAR | 32000 |
MCHAR or MVARCHAR | 32000 |
NCHAR or NVARCHAR | 16000 |
- The data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- Specifying 0 in argument-3 means that nothing is to be deleted.
- The following table lists the data types of the results.
Table 2-53 Data type of the result of the INSERTSTR system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
Table 2-54 Data type of the result of the INSERTSTR_LONG system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(32000) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(32000) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(16000) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- If the data type of the result is a character string data type, the character set of the result is the character set of argument-1.
- The following table indicates the ranges of values that can be specified in argument-2 and argument-3. Note that m is the value of argument-2.
Table 2-55 Ranges of specifiable values in argument-2 and argument-3 of the INSERTSTR system-defined scalar function
Data type of argument-1 | Range of values in argument-2 | Range of values in argument-3 |
---|
CHAR(n) or VARCHAR(n) | Default character set or a character set other than UTF-16 | 1 to n | 0 to (n + 1 - m) |
UTF-16 character set | 1 to n 2 | 0 to (n 2 + 1 - m) |
MCHAR(n) or MVARCHAR(n) | 1 to n | 0 to (n + 1 - m) |
NCHAR(n) or NVARCHAR(n) | 1 to n | 0 to (n + 1 - m) |
Table 2-56 Ranges of specifiable values in argument-2 and argument-3 of the INSERTSTR_LONG system-defined scalar function
Data type of argument-1 | Range of value of argument-2 | Range of value of argument-3 |
---|
CHAR(n) or VARCHAR(n) | Default character set or a character set other than UTF-16 | 1 to 32000 | 0 to (32001-m) |
UTF-16 character set | 1 to 16000 | 0 to (16001-m) |
MCHAR(n) or MVARCHAR(n) | 1 to 32000 | 0 to (32001-m) |
NCHAR(n) or NVARCHAR(n) | 1 to 16000 | 0 to (16001-m) |
- If the length (number of characters) of the character string specified in argument-1 is shorter (less) than character position m specified in argument-2, spaces as defined in the character set of argument-1 (double-byte spaces if the data type of argument-1 is NCHAR or NVARCHAR) are added until the number of characters reaches (m - 1), and no strings are deleted in any sections.
If the character string specified in argument-1 is longer than m but less than (m-1+nd), the characters beginning at position m through the last character are deleted (nd is the number of characters specified in argument-3).
- The length of the result must not exceed the maximum length for the result data type. If the length of the result will exceed the length of the argument-1 character string, we recommend that the INSERTSTR_LONG function be used.
(d) Examples
Examples of the INSERTSTR and INSERTSTR_LONG functions are shown below:
INSERTSTR('data warehouse system', 6, 9, 'base')
==> 'data base system'
INSERTSTR_LONG('data system', 6, 0, 'warehouse ')
==> 'data warehouse system'
INSRTSTR('data base management system', 11, 11, '')
==> 'data base system'
The character string specified in argument-4 has a length 0.
INSERTSTR_LONG('data base system', 31, 0, '')
==> 'data base system '
The result value contains 14 space characters following the string system.
(22) INTERVAL_DATETIMES
(a) Function
Returns the datetime interval between two time stamps in predefined character string representation ('YYYY-MM-DD hh:mm:ss') specified in the arguments, in terms of a decimal representation (
YYYYMMDDhhmmss.). If time-stamp-of-argument-1 < time-stamp-of-argument-2, the result is a negative value.
(b) Format
[MASTER.]INTERVAL_DATETIMES(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be a character string data type (CHAR or VARCHAR).
- Use the same character set as was used for the value expression specified in argument-1 and argument-2. In addition, use a character set other than UTF-16. If you specify a character string data type that uses the UTF-16 character set in argument-1 or argument-2, use the CAST specification to convert the character string data type to the default character set.
- The data type of the result will be DECIMAL(14).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- For the value of argument-1 and argument-2, specify a valid predefined character string representation ('YYYY-MM-DD hh:mm:ss') of the time stamp; do not specify a fractional second in the predefined character string representation of the time stamp.
- The result of INTERVAL_DATETIMES(time-stamp-1, time-stamp-2) is calculated according to the following rules:
- If time-stamp-1 < time-stamp-2:
Result = -INTERVAL_DATETIMES(time-stamp-1, time-stamp-2)
- If time-stamp-1
time-stamp-2:
If second of time-stamp-1
second of time-stamp-2:
Second of the result = second of time-stamp-1 - second of time-stamp-2
If second of time-stamp-1 < second of time-stamp-2:
Second of the result = second of time-stamp-1 - second of time-stamp-2 + 60
Minute of time-stamp-2 = minute of time-stamp-2 + 1
If minute of time-stamp-1
minute of time-stamp-2:
Minute of the result = minute of time-stamp-1 - minute of time-stamp-2
If minute of time-stamp-1 < minute of time-stamp-2:
Minute of the result = minute of time-stamp-1 - minute of time-stamp-2 + 60
Hour of time-stamp-2 = hour of time-stamp-2 + 1
If hour of time-stamp-1
hour of time-stamp-2:
Hour of the result = hour of time-stamp-1 - hour of time-stamp-2
If hour of time-stamp-1 < hour of time-stamp-2:
Hour of the result = hour of time-stamp-1 - hour of time-stamp-2 + 24
Day of time-stamp-2 = day of time-stamp-2 + 1
If day of time-stamp-1
day of time-stamp-2:
Day of the result = day of time-stamp-1 - day of time-stamp-2
If day of time-stamp-1 < day of time-stamp-2
Day of the result = day of time-stamp-1 - day of time-stamp-2 + last day of the month of time-stamp-2
Month of time-stamp-2 = month of time-stamp-2 + 1
If month of time-stamp-1 = month of time-stamp-2:
Month of the result = month of time-stamp-1 - month of time-stamp-2
If month of time-stamp-1 < month of time-stamp-2:
Month of the result = month of time-stamp-1 - month of time-stamp-2 + 12
Year of time-stamp-2 = year of time-stamp-2 + 1
Year of the result = year of time-stamp-1 - year of time-stamp-2
- Result = (years of result x 10000000000 + months of result x 100000000 + days of result x 1000000 + hours of result x 10000 + minutes of result x 100 + seconds of result)
(d) Examples
Examples of the INTERVAL_DATETIMES function are shown below:
INTERVAL_DATETIMES('2001-01-01 00:00:00',
'1999-12-31 23:59:59')
==> 10000000001.
INTERVAL_DATETIMES('1999-12-31 23:59:59',
'2001-01-01 00:00:00')
==> -10000000001.
INTERVAL_DATETIMES('1996-09-20 15:10:20',
'1956-06-07 03:15:30')
==> 400313115450.
NUMEDIT(INTERVAL_DATETIMES('1996-09-20 15:10:20',
'1956-06-07 03:15:30'),
'<9990" YEARS "90" MONTHS "90" DAYS "90" HOURS "
90" MINUTES "90" SECONDS"')
==> '40 years 3 months 13 days 11 hours 54 minutes 50 seconds'
(23) ISDIGITS
(a) Function
Returns a BOOLEAN value indicating whether or not the character string specified in an argument is composed solely of numeric digits.
(b) Format
[MASTER.]ISDIGITS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal or mixed character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR) or mixed character string data type (MCHAR or MVARCHAR) and its length may not exceed 60 bytes.
- If the data type of the argument is a character string type (CHAR, VARCHAR), use a character set other than UTF-16 for the data type. If you specify a character string data type that uses the UTF-16 character set in the argument, use the CAST specification to convert the character string data type to the default character set.
- The data type of the result will be BOOLEAN.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the character string specified in the argument consists solely of numeric digits (0 to 9), the result is TRUE; otherwise, the result is FALSE.
(d) Examples
Examples of the ISDIGITS function are shown below:
ISDIGITS('1234567890') ==> true
ISDIGITS('123ABC') ==> false
ISDIGITS('') ==> false
(24) IS_DBLBYTES
(a) Function
Returns a BOOLEAN value indicating whether or not the character string specified in an argument is composed solely of double-byte characters.
(b) Format
[MASTER.]IS_DBLBYTES(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal or mixed character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR) or mixed character string data type (MCHAR or MVARCHAR).
- If the data type of the argument is a character string data type (CHAR or VARCHAR), the default character set must be used when specifying the data type of the argument.
- The data type of the result will be BOOLEAN.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the character string specified in the argument consists of only single-byte characters, the result is TRUE; otherwise, the result is FALSE.
(d) Examples
Examples of the IS_DBLBYTES function are shown below:
![[Figure]](figure/zu2s030a.gif)
(25) IS_SNGLBYTES
(a) Function
Returns a BOOLEAN value indicating whether or not the character string specified in an argument is composed solely of single-byte characters.
(b) Format
[MASTER.]IS_SNGLBYTES(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal or mixed character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CAST specification
- CASE expression
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a character string data type (CHAR or VARCHAR) or mixed character string data type (MCHAR or MVARCHAR).
- If the data type of the argument is a character string type (CHAR or VARCHAR), the default character set must be used when specifying the data type of the argument.
- The data type of the result will be BOOLEAN.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the character string specified in the argument consists solely of single-byte characters, the result is TRUE; otherwise, the result is FALSE.
(d) Examples
Examples of the IS_SNGLBYTES function are shown below:
![[Figure]](figure/zu2s030b.gif)
(26) LAST_DAY
(a) Function
Returns the last day of the month for the date specified in an argument.
(b) Format
[MASTER.]LAST_DAY(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the LAST_DAY function are shown below:
LAST_DAY(DATE('1999-01-01')) ==> '1999-01-31'
LAST_DAY(DATE('1999-02-16')) ==> '1999-02-28'
LAST_DAY(DATE('1999-06-10')) ==> '1999-06-30'
LAST_DAY(DATE('1999-12-25')) ==> '1999-12-31'
LAST_DAY(DATE('2000-02-03')) ==> '2000-02-29'
(27) LEAST
(a) Function
Returns the smallest value among specified arguments.
(b) Format
[MASTER.]LEAST(argument, argument [, argument])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- A maximum of three arguments can be specified.
- The following can be specified in each value expression:
- Literal
- USER, CURRENT_DATE, or CURRENT_TIME
- Column specification
- SQL variable or SQL parameter
- Arithmetic, date, time, or concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- All specified arguments must be of one of the following data types: numeric, character string, mixed character string, national character string, date, or time.
- If the value expression specified in an argument is a character string data type, use the same character set for all value expressions.
- The following table lists the data types of the results.
Table 2-57 Data type of the result of the LEAST system-defined scalar function
Data type of integer | Data type of result |
---|
INTEGER or SMALLINT | INTEGER |
DECIMAL [, INTEGER or SMALLINT] | DECIMAL#1 |
FLOAT, SMALLFLT [, DECIMAL, INTEGER, or SMALLINT] | FLOAT |
CHAR or VARCHAR | VARCHAR#2 |
MCHAR or MVARCHAR | MVARCHAR#2 |
NCHAR or NVARCHAR | NVARCHAR#2 |
DATE | DATE |
TIME | TIME |
- #1: The following precision and scaling apply, where pi and si denote the precision and scaling of the ith argument, respectively:
- Precision = max(p1 - s1, p2 - s2, ...) + max(s1, s2, ...)
- Scaling = max(s1, s2, ...)
- A result whose precision exceeds 38 causes an error.
- INTEGER is treated as DECIMAL(10,0); SMALLINT is treated as DECIMAL(5,0).
- #2: The following maximum length applies, where ni denotes the maximum length of the ith argument (the defined length for a fixed-length data type):
- Maximum length = max(n1, n2, ...)
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- If the data type of the result is a character string data type, it uses the same character set as the argument.
(28) LEFTSTR
(a) Function
Returns from the beginning (the leftmost position) of the character string specified in argument-1 the substring consisting of the number of characters specified in argument-2.
(b) Format
[MASTER.]LEFTSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1:
- Character string data type using any character set (CHAR, VARCHAR)
- National character data type (NCHAR, NVARCHAR)
- Mixed character string data type (MCHAR, MVARCHAR)
- The data type of argument-2 must be INTEGER or SMALLINT.
- The following table indicates the range of values that can be specified in argument-2.
Table 2-58 Range of specifiable values in argument-2 of the LEFTSTR system-defined scalar function
Data type of argument-1 | Range of values in argument-2 |
---|
CHAR(n) or VARCHAR(n) | Default character set or a character set other than UTF-16 | 0 to n |
UTF-16 character set | 0 to n 2 |
MCHAR(n) or MVARCHAR(n) | 0 to n |
NCHAR(n) or NVARCHAR(n) | 0 to n |
- The following table lists the data types of the results.
Table 2-59 Data type of the result of the LEFTSTR system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If the number of characters in the argument-1 character string is less than the number of characters specified in argument-2, the result will be the argument-1 value.
- If the data type of the result is a character string type, it uses the same character set as argument-1.
(d) Examples
Examples of the LEFTSTR function are shown below:
LEFTSTR('data base system', 9) ==> 'data base'
LEFTSTR('DATA SYSTEM', 0) ==> ''(Character string of 0 length)
(29) LN
(a) Function
Returns the natural logarithm of an argument.
(b) Format
[MASTER.]LN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(30) LOG10
(a) Function
Returns the common logarithm of an argument.
(b) Format
[MASTER.]LOG10(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(31) LTRIM
(a) Function
Beginning at the left end of the character string specified in argument-1, removes all instances of each of the characters in the character string specified in argument-2 until a character not found in the argument-2 character string is encountered.
(b) Format
[MASTER.]LTRIM(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1 and argument-2:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- If the data type is a character string data type or mixed character string data type, the length of the value of argument-2 must be 30 bytes or less (60 bytes or less for a character string data type that uses the UTF-16 character set). If the data type is a national character string data type, the length of the value of argument-2 must be 30 characters or less.
- If the data type of both argument-1 and argument-2 is a character string type, use the same character set for the value expression.
- The following table lists the data types of the results.
Table 2-60 Data type of the result of the LTRIM system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- If the data type of the result is a character string type (CHAR or VARCHAR), it uses the same character set as argument-1.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If argument-2 is omitted, a space character of the appropriate data type is assumed.
(d) Examples
Examples of the LTRIM function are shown below:
LTRIM('abcabcabdata base', 'abc') ==> 'data base'
LTRIM(' data base') ==> 'data base'
(32) LTRIMSTR
(a) Function
Beginning at the left end of the character string specified in argument-1, deletes each successive occurrence of the character string specified in argument-2 until it does not find that character string.
(b) Format
[MASTER.]LTRIMSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1 and argument-2:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- If the data type of both argument-1 and argument-2 is a character string type, use the same character set for the value expression.
- The following table indicates the maximum length of argument-2.
Table 2-61 Maximum length of argument-2 of the LTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Maximum length of argument-2 |
---|
CHAR or VARCHAR | Default character set or a character set other than UTF-16 | 255 |
UTF-16 character set | 510 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
- The following table lists the data types of the results.
Table 2-62 Data type of the result of the LTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- If the data type of the result is a character string type (CHAR or VARCHAR), it uses the same character set as argument-1.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(d) Example
An example of the LTRIMSTR function is shown below:
LTRIMSTR('abcabcabdata base', 'abc') ==> 'abdata base'
(33) MIDNIGHTSECONDS
(a) Function
Returns the number of seconds from midnight to the time specified in an argument.
(b) Format
[MASTER.]MIDNIGHTSECONDS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Time operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be TIME.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- The result can be obtained from the following formula:
Result = (((hour of argument x 60) + minute of argument) x 60) + second of argument
(d) Examples
Examples of the MIDNIGHTSECONDS function are shown below:
MIDNIGHTSECONDS(TIME('23:59:59')) ==> 86399
MIDNIGHTSECONDS(TIME('14:14:14')) ==> 51254
(34) MONTHNAME
(a) Function
Returns the month name (such as "January" or "February") of the date specified in an argument.
(b) Format
[MASTER.]MONTHNAME(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result is VARCHAR(18).
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- The character set of the result is the default character set.
(d) Examples
Examples of the MONTHNAME function are shown below:
MONTHNAME(DATE('1999-01-01')) ==> 'January'
MONTHNAME(DATE('1999-02-28')) ==> 'February'
MONTHNAME(DATE('1999-03-03')) ==> 'March'
MONTHNAME(DATE('1999-04-01')) ==> 'April'
MONTHNAME(DATE('1999-05-05')) ==> 'May'
MONTHNAME(DATE('1999-06-07')) ==> 'June'
MONTHNAME(DATE('1999-07-07')) ==> 'July'
MONTHNAME(DATE('1999-08-15')) ==> 'August'
MONTHNAME(DATE('1999-09-23')) ==> 'September'
MONTHNAME(DATE('1999-10-10')) ==> 'October'
MONTHNAME(DATE('1999-11-11')) ==> 'November'
MONTHNAME(DATE('1999-12-31')) ==> 'December'
(35) MONTHS_BETWEEN
(a) Function
Returns as a real number (FLOAT type) the number of months between two dates specified in arguments.
(b) Format
[MASTER.]MONTHS_BETWEEN(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data types of argument-1and argument-2 must be DATE.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The number of months which is the result of MONTHS_BETWEEN is calculated according to the following rules:
- If date-1 < date-2:
Result = -MONTHS_BETWEEN (date-2, date-1)
- If date-1
date-2:
Let m be an integer greater than or equal to 0, and let date-4 and date-3 be the date (date-2 + m months) and (date-2 + (m + 1) months), respectively, such that date-4
date-1 < date-3
Let d1 and d2 be the number of days between date-4 and date-1 (DAYS (date-1) - DAYS (date-4)) and the number of days between date-4 and date-3 (DAYS (date-3) - DAYS (date-4)), respectively (where d2 is one of the following values: 28, 29, 30, 31)
The number of months in the result will be (m + d1
d2) months.
The following figure shows the relationships between the dates date-1, date-2, date-3, and date-4.
Figure 2-7 Relationships between the dates date-1, date-2, date-3, and date-4
![[Figure]](figure/zu2s0450.gif)
(d) Examples
Examples of the MONTHS_BETWEEN function are shown below:
MONTHS_BETWEEN(DATE('1999-07-10'), DATE('1999-06-10'))
==> 1
MONTHS_BETWEEN(DATE('1999-07-11'), DATA('1999-06-10'))
==> 1.032258...
MONTHS_BETWEEN(DATE('1999-06-11'), DATA('1999-05-10'))
==> 1.033333...
MONTHS_BETWEEN(DATE('1999-02-11'), DATA('1999-01-10'))
==> 1.035714...
MONTHS_BETWEEN(DATE('2000-02-11'), DATA('2000-01-10'))
==> 1.034482...
MONTHS_BETWEEN(DATE('1999-09-09'), DATA('1999-06-10'))
==> 2.967741...
MONTHS_BETWEEN(DATE('1999-06-10'), DATA('1999-09-09'))
==> -2.967741...
(36) NEXT_DAY
(a) Function
Returns the next date after the date specified in argument-1 that is the same day of the week as the weekday number specified in argument-2 (where Sunday is weekday 1).
(b) Format
[MASTER.]NEXT_DAY(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data type of argument-2 must be INTEGER or SMALLINT.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The following table lists the meanings of the integer values specified in argument-2.
Table 2-63 Meanings of integer values specifiable in argument-2 (integer values for days of the week)
Integer value of argument-2 | Day of week |
---|
1 | Sunday |
2 | Monday |
3 | Tuesday |
4 | Wednesday |
5 | Thursday |
6 | Friday |
7 | Saturday |
- If the NEXT_DAY function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(d) Examples
Examples of the NEXT_DAY function are shown below:
NEXT_DAY(DATE('1999-06-10'), 1) ==> '1999-06-13'
NEXT_DAY(DATE('1999-06-10'), 4) ==> '1999-06-16'
NEXT_DAY(DATE('1999-06-10'), 5) ==> '1999-06-17'
NEXT_DAY(DATE('1999-06-10'), 6) ==> '1999-06-11'
NEXT_DAY(DATE('1999-06-10'), 7) ==> '1999-06-12'
1999.06.10 was a Thursday.
(37) NUMEDIT
(a) Function
Converts the numeric value specified in argument-1 into character string representation by editing it according to the format specified in argument-2.
(b) Format
[MASTER.]NUMEDIT(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a numeric data type.
- argument-2 must be a character string data type (CHAR or VARCHAR). The length of the value of argument-2 must not exceed 250 bytes.
- Use a character set other than UTF-16 for the data type of argument-2. If you specify a character string data type that uses the UTF-16 character set in argument-2, use the CAST specification to convert the character string data type to the default character set.
- The data type of the result is VARCHAR(255).
- The character set of the result is the character set used for argument-2.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The value of argument-2 must be specified in the following format:
["character-string"] [+]
{[{<|>}] {9|0|,|"character-string"}...
[,{9|0|,|"character-string"}...]
|{E|e} .integer ["character-string"]}
- "character-string"
- A character string enclosed in double quotation marks represents itself.
- To specify double quotation marks within a character string enclosed in double quotation marks, specify two sets of double quotation marks in succession.
- +
- The [+] sign specifies the method of sign representation.
- + uses the minus sign (-) to represent argument-1 as a negative value; otherwise, it uses a plus sign (+) to represent the value.
- The default for the '+' symbol is a minus sign (-) if the value of argument-1 is negative, and a space character otherwise.
- {< | >}
- The {< | >} symbols specify justification of the character representation of the numeric value.
- The "<" symbol removes all space characters and shifts the character representation to the left.
- The ">" symbol removes all space characters, shifts the character representation to the right, and pads the left side with space characters.
- If neither "<" nor ">" is specified, the character representation is not shifted.
- { 9 | 0 | , | "character-string"} ... [, { 9 | 0 | , | "character-string"}] ...
- The numbers "9" and "0" each represent one digit of the numeric value so that the number of digits associated with the numeric value of argument-1 can be edited.
- The total number of "9"s and "0"s represents the precision of the numeric value after being edited.
- Specifying "0"s produces an edit result equal in digit positions to the number of "0"s specified.
- Specifying a "," produces an edit result that is the comma itself if the edit results on both sides of "," are numeric characters; otherwise, the comma is replaced with one space character.
- The symbol "." represents the decimal point.
- The total number of "9"s and "0"s following the "." indicates the scaling of the numeric value after editing.
- Specifying a "9" before a "." causes the result of editing by "9" to be a space character, provided that the corresponding digit is 0 and the "9" is the first digit, or the edit result to the left of the result of editing by "9" is not a numeric character (except when it is associated with a comma); otherwise, the result of editing by "9" will be a numeric character in the corresponding digit position.
- Specifying a "9" after a "." causes the result of editing by "9" to be an empty (0) character, provided that the corresponding digit is 0 and the "9" is the last digit, or the edit result to the right of the result of editing by "9" is not a numeric character (except when it is associated with a comma); otherwise, the result of editing by "9" will be a numeric character in the corresponding digit position.
- { E | e }.integer is specified in floating-point decimal format.
- The result of the editing is a floating-point decimal format with a scaling for the mantissa specified in integer. The scaling for the mantissa must not exceed 30.
- If the value of argument-1 is a value that cannot be represented by the format specified in argument-2, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- If low-order digits are truncated by the editing, the result value is rounded off.
(d) Examples
Examples of the NUMEDIT function are shown below:
NUMEDIT(1234567.89, '99,999,990.00"$"') ==>' 1,234,567.89$'
NUMEDIT(1000, '99,999,990.00"$"') ==>' 1,000.00$'
NUMEDIT(1234567.89, '" $"99,999,990.00') ==>' $ 1,234,567.89'
NUMEDIT(1000, '" $"99,999,990.00') ==>' $ 1,000.00'
NUMEDIT(1234567.89, '"$"+99,999,990.00') ==>'$+ 1,234,567.89'
NUMEDIT(-1000, '"$"+99,999,990.00') ==>'$- 1,000.00'
NUMEDIT(1234567.89, '"$">99,999,990.00') ==>' $1,234,567.89'
NUMEDIT(1000, '"$">99,999,990.00') ==>' $1,000.00'
NUMEDIT(1234567.89, '"$"<99,999,990.00') ==>'$1,234,567.89'
NUMEDIT(1000, '"$"<99,999,990.00') ==>'$1,000.00'
NUMEDIT(0.5, '"$"<99,999,990.00') ==>'$0.50'
NUMEDIT(1234567.89, '+E.10"$"') ==>'+1.2345678900E+0.6$'
NUMEDIT(1234567.89, '"$"+e.10') ==>'$+1.2345678900e+0.6'
(38) PI
(a) Function
Returns
, the value of the circle constant.
(b) Format
[MASTER.]PI()
(c) Rules
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). However, because the value of
is always returned, the null value will never be returned.
(d) Example
An example of the PI function is shown below:
PI() ==> 3.14159265358979323846
(39) POSSTR
(a) Function
If in the character string specified in argument-1 the substring specified in argument-2 occurs at least the number of times (nd) specified in argument-4 at or after the character position specified in argument-3, returns the starting position (character position) of the ndth substring.
(b) Format
[MASTER.]POSSTR(argument-1, argument-2, [, argument-3
[, argument-4]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each of the value expressions of argument-1 and argument-2:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in each of the value expressions of argument-3 and argument-4:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1 and argument-2:
- Character string data type using any character set (CHAR, VARCHAR)
- National character data type (NCHAR, NVARCHAR)
- Mixed character string data type (MCHAR, MVARCHAR)
- If the data type of both argument-1 and argument-2 is a character string data type (CHAR, VARCHAR), use the same character set for the value expression.
- The following table indicates the maximum length of the value of argument-2.
Table 2-64 Maximum length of argument-2 of the POSSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Maximum length of argument-2 |
---|
CHAR or VARCHAR | Default character set or a character set other than UTF-16 | 255 |
UTF-16 character set | 510 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
- The data types of argument-3 and argument-4 must be INTEGER or SMALLINT.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- The following table indicates the ranges of values that can be specified in argument-3 and argument-4.
Table 2-65 Ranges of specifiable values in argument-3 and argument-4 of the POSSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Range of values in argument-3 | Range of values in argument-4 |
---|
CHAR(n) or VARCHAR(n) | Default character set or a character set other than UTF-16 | 1 to n | 1 to n |
UTF-16 character set | 1 to n 2 | 1 to n 2 |
MCHAR(n) or MVARCHAR(n) | 1 to n | 1 to n |
NCHAR(n) or NVARCHAR(n) | 1 to n | 1 to n |
- The default for argument-3 is 1; the default for argument-4 is also 1.
- If the length (number of characters) of the character string in argument-2 is 0, the result will be the value of argument-3.
- If the substring matching the character string specified in argument-2 occurs more than the number of times (nd) specified in argument-4 at or after the character position (m) specified in argument-3 in the character string specified in argument-1, the result will be a value greater than or equal to m and equal to the number of characters in the character string specified in argument-1 that are to the left of the beginning of the ndth substring, plus 1. It should be noted that these substrings that match the character string specified in argument-2 are not duplicate substrings. If the substring matching the character string specified in argument-2 does not occur more than the number of times (nd) specified in argument-4 at or after the character position (m) specified in argument-3 in the character string specified in argument-1, the result will be 0.
(d) Examples
Examples of the POSSTR function are shown below:
POSSTR(data base system', 'a') ==> 2
POSSTR(data base system', '') ==> 1 (argument-2 is
POSSTR(data base system', 'a', 5) ==> 7 character string
POSSTR(data base system', 'st') ==> 13 of 0 length.)
POSSTR(data base system', 'a', 1, 3) ==> 7
POSSTR(data base system', 'manager') ==> 0
(40) POWER
(a) Function
Returns the nth power of the value of argument-1, where n denotes the value of argument-2.
(b) Format
[MASTER.]POWER(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 and argument-2 must both be a numeric data type.
- The following table lists the data types of the results.
Table 2-66 Data type of the result of the POWER system-defined scalar function
Data type of argument-1 | Data type of argument-2 |
---|
SMALLINT or INTEGER | DECIMAL(p,s), SMALLFLT, or FLOAT |
---|
SMALLINT or INTEGER | INTEGER | FLOAT |
DECIMAL(p, s), SMALLFLT, or FLOAT | FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- Specifying a negative value in argument-1 or a non-integer value in argument-2 causes a definition area error (domain error occurs).
- Specifying the value 0 in argument-1 and a non-positive value in argument-2 causes division by zero (division by zero). For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- If the POWER function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(41) QUARTER
(a) Function
Based on a month specified in argument-2 and a day specified in argument-3 as the beginning of the fiscal year, returns an integer (1, 2, 3, or 4) indicating the fiscal-year quarter in which the date specified in argument-1 falls.
(b) Format
[MASTER.]QUARTER(argument-1 [, argument-2 [, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE.
- The data types and argument-2 and argument-3 must be INTEGER or SMALLINT.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- For argument-2, the default is 1 and the range of specifiable values is 1 to 12.
- For argument-3, the default is 1 and the range of specifiable values is 1 to 29 when the value of argument-2 is 2 and 1 to (number of days in the month specified in argument-2) in all other cases.
- The result is determined according to the following rules:
- If day of argument-1 date < argument-3 day:
Number of months = month of argument-1 date - argument-2 month - 1
- If day of argument-1 date
argument-3:
Number of months = month of argument-1 date - argument-2 month
- If number of months < 0:
Result = (number of months + 12)
4 + 1
- If number of months
0:
Result = number of months
4 + 1
(d) Examples
Examples of the QUARTER function are shown in the following:
QUARTER(DATE('1999-01-01')) ==> 1
QUARTER(DATE('1999-09-10')) ==> 3
QUARTER(DATE('1999-12-31')) ==> 4
QUARTER(DATE('1999-04-01'), 4) ==> 1
QUARTER(DATE('1999-09-10'), 4) ==> 2
QUARTER(DATE('1999-03-31'), 4) ==> 4
QUARTER(DATE('1999-03-21'), 3, 21) ==> 1
QUARTER(DATE('1999-09-20'), 3, 21) ==> 2
QUARTER(DATE('1999-03-20'), 3, 21) ==> 4
(42) RADIANS
(a) Function
Converts to radians the angle specified in degrees in an argument.
(b) Format
[MASTER.]RADIANS(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(43) REPLACE (REPLACE_LONG)
(a) Function
Replaces in the character string specified in argument-1 all instances of the character string specified in argument-2 with the character string specified in argument-3.
(b) Format
[MASTER.]REPLACE(argument-1, argument-2[, argument-3])
[MASTER.]REPLACE_LONG(argument-1, argument-2, argument-3)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1, argument-2, and argument-3:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- The following table indicates the maximum lengths of the values of argument-2 and argument-3.
Table 2-67 Maximum lengths of argument-2 and argument-3 of the REPLACE and REPLACE_LONG system-defined scalar functions
Data type of argument-1 (and of argument-2 and argument-3) | Maximum length of argument-2 and argument-3 |
---|
CHAR or VARCHAR | Default character set or a character set other than UTF-16 | 255 |
UTF-16 character set | 510 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
- If the data type of argument-1, argument-2, and argument-3 are character string types (CHAR or VARCHAR), use the same character set for the value expressions specified in the arguments.
- If argument-3 is omitted, an empty character string is assumed, and the function deletes all substrings matching the character string specified in argument-2 from the character string specified in argument-1.
- The following table lists the data types of the results.
Table 2-68 Data type of the result of the REPLACE system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
Table 2-69 Data type of the result of the REPLACE_LONG system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(32000) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(32000) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(16000) |
- If the data type of the result is a character string type (CHAR or VARCHAR), it uses the same character set as argument-1.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- The length of the result must not exceed the maximum length for the result data type. If the length of the result will exceed the length of the argument-1 character string, we recommend that the REPLACE_LONG function be used.
(d) Examples
Examples of the REPLACE function are shown below:
REPLACE('a big dog and a small dog', 'dog', 'cat')
==> 'a big cat and small cat'
REPLACE('a big dog and a small dog', 'big ')
==> 'a dog and a small dog'
(44) REVERSESTR
(a) Function
Returns a character string that is the reverse of the character string specified in an argument (the returned character string reads from left to right the same as the specified character string reads from right to left).
(b) Format
[MASTER.]REVERSESTR(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of the argument:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- The following table lists the data types of the results.
Table 2-70 Data type of the result of the REVERSESTR system-defined scalar function
Data type of argument | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- If the data type of the result is a character string type (CHAR or VARCHAR), it uses the same character set as the argument.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the REVERSESTR function are shown below:
REVERSESTR('data base') ==> 'esab atad'
REVERSESTR('esab atad') ==> 'data base'
REVERSESTR(' esab atad') ==> 'data base '
(45) RIGHTSTR
(a) Function
Returns from the end (the rightmost position) of the character string specified in argument-1 the substring consisting of the number of characters specified in argument-2.
(b) Format
[MASTER.]RIGHTSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression of argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- The data type of argument-2 must be INTEGER or SMALLINT.
- The following table indicates the range of values that can be specified in argument-2.
Table 2-71 Range of specifiable values in argument-2 of the RIGHTSTR system-defined scalar function
Data type of argument-1 | Range of values in argument-2 |
---|
CHAR(n) or VARCHAR(n) | Default character set or a character set other than UTF-16 | 0 to n |
UTF-16 character set | 0 to n 2 |
MCHAR(n) or MVARCHAR(n) | 0 to n |
NCHAR(n) or NVARCHAR(n) | 0 to n |
- The following table lists the data types of the results.
Table 2-72 Data type of the result of the RIGHTSTR system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- If the data type of the result is a character string type (CHAR or VARCHAR), it uses the same character set as the numerical value of argument-1.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If the number of characters in the argument-1 character string is less than the number of characters specified in argument-2, the result will be the argument-1 value.
(d) Examples
Examples of the RIGHTSTR function are shown below:
RIGHTSTR('data base system', 6) ==> 'system'
RIGHTSTR('data system', 0) ==> ''
(Character string of 0 length)
(46) ROUND
(a) Function
Rounds the value of argument-1 following the nth place after the decimal point (number of digits following the decimal point given by 10 -n), where n is the value of argument-2.
Specifying argument-3 causes the function to round up if the value of argument-1 at the rightmost digit position given by 1 x 10 (-n-1) is greater than or equal to the value of argument-3; otherwise, the function rounds down.
(b) Format
[MASTER.]ROUND(argument-1, [, argument-2[, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a numeric data type; the data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- The default for argument-2 is 0. The following table indicates the range of values that can be specified in argument-2.
Table 2-73 Range of specifiable values in argument-2 of the ROUND system-defined scalar function
Data type of result | Range of values in argument-2 |
---|
INTEGER | -9 to 0 |
DECIMAL(p, s) | -(p - s- 1) to s |
FLOAT | HP-UX (32-bit mode): -307 to 307 HP-UX (64-bit mode), Solaris, AIX, Linux, and Windows: -307 to 323 |
- The following table lists the data types of the results.
Table 2-74 Data type of the result of the ROUND system-defined scalar function
Data type of argument-1 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- The default for argument-3 is 5. The range of values that can be specified in argument-3 is 1 to 9. If the value of argument-1 at the rightmost digit position given by 1 x 10 (-n-1) is greater than or equal to the value of argument-3, the value of argument-1 is rounded up; otherwise, it is rounded down.
- If the ROUND function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(47) ROUNDMONTH
(a) Function
Rounds off the date specified in argument-1 to the first day of either the specified date's month or the following month, using the day of the month specified in argument-2 to determine the point for rounding up to the following month. This function can be used, for example, to change a date on or after the 20th of the month to the first of the next month.
(b) Format
[MASTER.]ROUNDMONTH(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE; the data type of argument-2 must be INTEGER or SMALLINT.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The default for argument-2 is 16. The range of values that can be specified in argument-2 is 1 to 32.
- The result is determined according to the following rules:
- If day of argument-1 < argument-2:
Year of result = year of argument-1
Month of result = month of argument-1
Day of result = 1
- If day of argument-1
argument-2:
If month of argument-1 < 12:
Year of result = year of argument-1
Month of result = month of argument-1 + 1
Day of result = 1
If month of argument-1 = 12:
Year of result = year of argument-1 + 1
Month of result = 1
Day of result = 1
- If the result of the ROUNDMONTH function is not within the range 0001/01/01 - 9999/12/31, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(d) Examples
Examples of the ROUNDMONTH function are shown below:
ROUNDMONTH(DATE('1999-08-15')) ==> '1999-08-01'
ROUNDMONTH(DATE('1999-08-16')) ==> '1999-09-01'
ROUNDMONTH(DATE('1999-09-20'), 21) ==> '1999-09-01'
ROUNDMONTH(DATE('1999-09-21'), 21) ==> '1999-10-01'
ROUNDMONTH(DATE('1999-12-21'), 21) ==> '2000-01-01'
ROUNDMONTH(DATE('1999-03-31'), 32) ==> '1999-03-01'
ROUNDMONTH(DATE('1999-02-29'), 29) ==> '2000-03-01'
ROUNDMONTH(DATE('1999-03-31'), 29) ==> '2000-04-01'
ROUNDMONTH(DATE('9999-12-31'), 26) ==> Overflow
(48) RTRIM
(a) Function
Beginning at the right end of the character string specified in argument-1, removes all instances of each of the characters in the character string specified in argument-2 until a character not found in the argument-2 character string is encountered.
(b) Format
[MASTER.]RTRIM(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1 and argument-2:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- If the data type of both argument-1 and argument-2 is a character string type (CHAR or VARCHAR), use the same character set for the value expressions specified in the arguments.
- If the data type is a character string type or mixed character string type, the length of the value of argument-2 must be 30 bytes or less (60 bytes or less for a character string data type that uses the UTF-16 character set). If the data type is a national character string type, the length of the value of argument-2 must be 30 characters or less.
- The following table lists the data types of the results.
Table 2-75 Data type of the result of the RTRIM system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- If the data type of the result is a character string type (CHAR or VARCHAR), it uses the same character set as argument-1.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- If argument-2 is omitted, one space character in the data type of the result is assumed.
(d) Examples
Examples of the RTRIM function are shown below:
RTRIM('data basebcabcabc', 'abc') ==> 'data base'
RTRIM('database ') ==> 'data base'
(49) RTRIMSTR
(a) Function
Beginning at the right end of the character string specified in argument-1, deletes each successive occurrence of the character string specified in argument-2 until it does not find that character string.
(b) Format
[MASTER.]RTRIMSTR(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- Specify one of the following for the data type of argument-1 and argument-2:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- If the data type of both argument-1 and argument-2 is a character string type (CHAR or VARCHAR), use the same character set for the value expressions specified in the arguments.
- The following table indicates the maximum length of argument-2.
Table 2-76 Maximum length of argument-2 of the RTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Maximum length of argument-2 |
---|
CHAR or VARCHAR | Default character set or a character set other than UTF-16 | 255 |
UTF-16 character set | 510 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR | 127 |
- The following table lists the data types of the results.
Table 2-77 Data type of the result of the RTRIMSTR system-defined scalar function
Data type of argument-1 (and argument-2) | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
- If the data type of the result is a character string type (CHAR or VARCHAR), it uses the same character set as argument-1.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(d) Example
An example of the RTRIMSTR function is shown below:
RTRIMSTR('data basebcabcabc', 'abc') ==> 'data basebc'
(50) SIGN
(a) Function
Returns the sign of an argument (+1 for positive, -1 for negative, 0 for zero).
(b) Format
[MASTER.]SIGN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The following table lists the data types of the results.
Table 2-78 Data type of the result of the SIGN system-defined scalar function
Data type of argument-1 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(1,0) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(51) SIN
(a) Function
Returns the sine (SIN trigonometric function) of an argument in which an angle is specified in radians.
(b) Format
[MASTER.]SIN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(52) SINH
(a) Function
Returns the hyperbolic sine of an argument.
(b) Format
[MASTER.]SINH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the SINH function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(53) SQRT
(a) Function
Returns the square root of the value of an argument.
(b) Format
[MASTER.]SQRT(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- Specifying a negative value in the argument causes a definition area error (domain error occurs).
(54) STRTONUM
(a) Function
Converts the character string representation of the numeric value specified in argument-1 into a numeric data type. Conversion is into INTEGER when the data type of argument-2 is INTEGER or SMALLINT; into DECIMAL with the same precision and scaling when the data type of argument-2 is DECIMAL; and into FLOAT when the data type of argument-2 is FLOAT or SMALLFLT.
(b) Format
[MASTER.]STRTONUM(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression for argument-1:
- Character string literal
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expression for argument-2:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called..
- argument-1 must be a character string data type (CHAR or VARCHAR). The length of the character string specified in argument-1 must not exceed 255 bytes.
- Use a character set other than UTF-16 for the data type of argument-1. If you specify a character string data type that uses the UTF-16 character set in argument-1, use the CAST specification to convert the character string data type to the default character set.
- argument-2 must be a numeric data type.
- The following table lists the data types of the results.
Table 2-79 Data type of the result of the STRTONUM system-defined scalar function
Data type of argument-2 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 is the null value, the result is also the null value. The value of argument-2 does not affect the result.
- Specify the value of the character string in argument-1 in the following format:
- Data type of argument-2 is INTEGER:
[space-character...] [+|-] [space-character ...] numeric-character...
[space-character ...]
- Data type of argument-2 is DECIMAL:
[space-character...] [+|-] [space-character...]
|numeric-character...[. [numeric-character...]]
| .numeric-character...| [space-character...]
- Data type of argument-2 is FLOAT:
[space-character...] [+|-] [space-character...]
|numeric-character... [. [numeric-character...]]
| .numeric-character...|
[{E|e} [+|-] numeric-character...] [space-character...]
- If the STRTONUM function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
- When the data type of argument-2 is DECIMAL and low-order digits are truncated by the data type conversion, the result value is rounded off.
(d) Examples
Examples of the STRTONUM function are shown below:
- When argument-2 is INTEGER:
STRTONAM(' - 1234567 ', 0)
==> -1234567 (INTEGER type)
STRTONUM(' + 1234567890123 ', 0)
==> Overflow
STRTONUM(' 1234567.89 ', 9)
==> Invalid value in argument-1 in STRTONUM function
during conversion to INTEGER
- When argument-2 is DECIMAL:
STRTONUM(' -1234567 ', 123456789012.
==> -1234567 (DECIMAL(12, 0) type)
STRTONUM(' 1234567.89 ', 9999999999.999)
==. 1234567.89 (DECIMAL(13, 3) type)
STRTONUM(' 1234567.89 ', 99999.999)
==> Overflow
STRTONUM(' 1.23456789E6 ', 9999999999.999)
==> Invalid value in argument-1 in STRTONUM function
during conversion to DECIMAL
- When argument-2 is FLOAT:
STRTONUM(' 1234567.89 ', 1e0)
==> 1.23456789e6 (FLOAT type)
STRTONUM(' -1234567890123 ', 0e0)
==> -1.234567890123E12 (FLOAT type)
STRTONUM(' 1.23456789E6 ', 1E1)
==> 1.23456789E6 (FLOAT type)
STRTONUM(' 1.0E310 ', 9E9)
==> Overflow
(55) TAN
(a) Function
Returns the tangent (TAN trigonometric function) of an argument in which an angle is specified in radians.
(b) Format
[MASTER.]TAN(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the TAN function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(56) TANH
(a) Function
Returns the hyperbolic tangent of an argument.
(b) Format
[MASTER.]TANH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The argument must be a numeric data type.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
- If the TANH function's result is a value that cannot be represented by the result data type, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(57) TRANSL (TRANSL_LONG)
(a) Function
If any of the characters in the character string specified in argument-2 is included in the character string specified in argument-1, returns a character string in which those characters are translated into the corresponding characters in the character string specified in argument-3, where the ith character in the character string specified in argument-2 corresponds to the ith character in argument-3. If the number of characters in argument-3 is less than the number of characters in the character string of argument-2, for the character string in argument-3, HiRDB assumes a character string that is obtained by repeatedly filling the character string in argument-3 with the characters in argument-4.
(b) Format
[MASTER.]TRANSL(argument-1, argument-2, argument-3[, argument-4])
[MASTER.]TRANSL_LONG(argument-1, argument-2, argument-3
[, argument-4])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Character string literal, mixed character string literal, or national character string literal (for TRANS_LONG, mixed character string literal only)
- Column specification
- SQL variable or SQL parameter
- Concatenation operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- If there are fewer characters in the character string specified in argument-3 than in the character string specified in argument-2, the character specified in argument-4 is used to pad out the argument-3 character string to the same length as the argument-2 character string.
- Only one character can be specified in argument-4. If argument-4 is omitted, a space from the character set used for argument-1 (double-byte spaces if the data type of argument-1 is NCHAR or NVARCHAR) is assumed.
- For TRANSL, specify one of the following for the data type of all arguments argument-1, argument-2, argument-3, and argument-4:
- Character string type using any character set (CHAR, VARCHAR)
- National character string type (NCHAR, NVARCHAR)
- Mixed character string type (MCHAR, MVARCHAR)
- For the TRANSL_LONG function, the data type of all arguments must be MCHAR or MVARCHAR.
- If the data type of all arguments argument-1, argument-2, argument-3, and argument-4 for TRANSL is a character string type (CHAR or VARCHAR), use the same character set for the value expressions specified in the arguments.
- The following table indicates the maximum lengths of the values of argument-2 and argument-3.
Table 2-80 Maximum lengths of argument-2 and argument-3 of the TRANSL and TRANSL_LONG system-defined scalar functions
Data type of argument-1 | Maximum length of argument-2 and argument-3 |
---|
CHAR or VARCHAR# | Default character set or a character set other than UTF-16 | 255 |
UTF-16 character set | 510 |
MCHAR or MVARCHAR | 255 |
NCHAR or NVARCHAR# | 127 |
#: Not specifiable for TRANSL_LONG.
- The following table lists the data types of the results.
Table 2-81 Data type of the result of the TRANSL system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | VARCHAR(n) |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(n) |
NCHAR(n) or NVARCHAR(n) | NVARCHAR(n) |
Table 2-82 Data type of the result of the TRASNSL_LONG system-defined scalar function
Data type of argument-1 | Data type of result |
---|
CHAR(n) or VARCHAR(n) | Not specifiable |
MCHAR(n) or MVARCHAR(n) | MVARCHAR(32000) |
NCHAR(n) or NVARCHAR(n) | Not specifiable |
- If the data type of the result of TRANSL is a character string type (CHAR or VARCHAR), it uses the same character set as argument-1.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
(d) Examples
Examples of the TRANSL and TRANSL_LONG functions are shown below:
TRANSL('data base system', 'abcdefghijklmnopqrstuvwxyz',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
==> 'DATA BASE SYSTEM' (Translation of lowercase characters
into uppercase characters)
TRANSL('<data base> system', '[ ] { } ( ) < >', '')
==> ' data base system '
(argument-3 is a character string with a length of 0, and all
enclosure symbols (such as [, ], {, }, (, ), <, >) are translated into spaces)
TRANSL('+12345.678', '+-012345678', 'SS', '9')
==> 'S99999.999'
TRANSL (M' d a t a b a s e s y s t e m'
M' 0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n
o p q r s t u v w x y z'
M'0123456789abcdefghijklmnopqrstuvwxyz')
==> M'data base system' (Translation of double-byte characters
into single-byte characters)
TRANSL_LONG(M'data base system',
M'0123456789abcdefghijklmnopqrstuvwxyz',
M'0 1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n
o p q r s t u v w x y z')
==> M'd a t a b a s e s y s t e m'
(Translation of single-byte characters into double-byte characters)
TRANSL('+12345.678', '+012345678', '', 'S')
==> 'SSSSSS.SSS'
TRANSL('2000-03-31 12:23:30', '-:', '/.')
==> '2000/03/31 12.23.30'
(58) TRUNC
(a) Function
Returns the value obtained by rounding off the value of argument-1 at the nth place after the decimal point (number of digits following the decimal point given by 10-n), where n is the value specified in argument-2.
(b) Format
[MASTER.]TRUNC(argument-1 [, argument-2])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- Numeric literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- argument-1 must be a numeric data type; the data type of argument-2 must be INTEGER or SMALLINT.
- The default for argument-2 is 0. The following table indicates the range of values that can be specified in argument-2.
Table 2-83 Range of specifiable values in argument-2 of the TRUNC system-defined scalar function
Data type of result | Range of values in argument-2 |
---|
INTEGER | -9 to 0 |
DECIMAL(p, s) | -(p - s - 1) to s |
FLOAT | HP-UX (32-bit mode): -307 to 307 HP-UX (64-bit mode) Solaris, AIX, Linux, and Windows: -307 to 323 |
- The following table lists the data types of the results.
Table 2-84 Data type of the result of the TRUNC system-defined scalar function
Data type of argument-1 | Data type of result |
---|
SMALLINT | INTEGER |
INTEGER | INTEGER |
DECIMAL(p, s) | DECIMAL(p, s) |
SMALLFLT | FLOAT |
FLOAT | FLOAT |
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
(59) TRUNCYEAR
(a) Function
Based on a month specified in argument-2 and a day specified in argument-3 as the beginning of the fiscal year, returns the first day of the fiscal year in which the date specified in argument-1 falls. This function can be used to determine a fiscal year that ends, for example, on March 20 or on the last day of March.
(b) Format
[MASTER.]TRUNCYEAR(argument-1[, argument-2[, argument-3]])
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in the value expression of argument-1:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- The following can be specified in the value expressions of argument-2 and argument-3:
- Integer literal
- Column specification
- SQL variable or SQL parameter
- Arithmetic operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of argument-1 must be DATE.
- The data types of argument-2 and argument-3 must be INTEGER or SMALLINT.
- The data type of the result will be DATE.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of any argument is the null value, the result is also the null value.
- For argument-2, the default is 1 and the range of values that can be specified is 1 to 12.
- For argument-3, the default is 1 and the range of values that can be specified is 1 to 29 when the value of argument-2 is 2 and 1 to (number of days in the month specified in argument-2) in all other cases.
- The result is determined according to the following rules:
- If month of argument-1 < argument-2 or month of argument-1 = argument-2 and day of argument-1 < argument-3:
Year of result = year of argument-1 - 1
- If month of argument-1 > argument-2 or month of argument-1 = argument-2 and day of argument-1
argument-3:
Year of result = year of argument-1
- If year of result is non-leap year and argument-2 = 2 and argument-3 = 29:
Month of result = 3
Day of result = 1
- If the year of the result is a leap year and argument-2 is not 2, or argument-3 is not 29:
Month of result = argument-2
Day of result = argument-3
- If the result of the TRUNCYEAR function is not within the range 0001/01/01 - 9999/12/31, an overflow error will result. For details about the operational results when overflow error suppression is in effect, see 2.18 Operational results with overflow error suppression specified.
(d) Examples:
Examples of the TRUNCYEAR function are shown below:
TRUNCYEAR(DATE('1999-09-10')) ==> '1999-01-01'
TRUNCYEAR(DATE('1999-09-11'), 4) ==> '1999-04-01'
TRUNCYEAR(DATE('1999-03-31'), 4) ==> '1999-04-01'
TRUNCYEAR(DATE('1999-08-11'), 3, 21) ==> '1999-03-21'
TRUNCYEAR(DATE('1999-03-20'), 3, 21) ==> '1999-03-21'
TRUNCYEAR(DATE('2000-02-28'), 2, 29) ==> '1999-03-01'
(1999-02-29 is a nonexistent date.)
TRUNCYEAR(DATE('2000-03-20'), 2, 29) ==> '2000-02-29'
TRUNCYEAR(DATE('0001-03-20'), 4) ==> Overflow
(60) WEEK
(a) Function
Returns an integer, in the range 1 to 54, that represents the number of the week since the beginning of the year in which the date specified in an argument falls (assuming that each week begins on Sunday).
(b) Format
[MASTER.]WEEK(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the WEEK function are shown below:
WEEK(DATE('2000-01-01')) ==> 1
WEEK(DATE('2000-01-02')) ==> 2
WEEK(DATE('2000-02-29')) ==> 10
WEEK(DATE('2000-12-30')) ==> 53
WEEK(DATE('2000-12-31')) ==> 54
2000-01-01 is a Saturday, and 2000-12-31 is a Sunday.
(61) WEEKOFMONTH
(a) Function
Returns an integer, in the range 1 to 6, that represents the number of the week since the beginning of the month in which the date specified in an argument falls.
(b) Format
[MASTER.]WEEKOFMONTH(argument)
(c) Rules
- The following can be specified in the argument:
- value-expression [AS data-type]
- The following can be specified in the value expression:
- CURRENT_DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in the value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data type of the argument must be DATE.
- The data type of the result will be INTEGER.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of the argument is the null value, the result is also the null value.
(d) Examples
Examples of the WEEKOFMONTH function are shown below:
WEEKOFMONTH(DATE('2000-01-01')) ==> 1
WEEKOFMONTH(DATE('2000-01-02')) ==> 2
WEEKOFMONTH(DATE('2000-01-29')) ==> 5
WEEKOFMONTH(DATE('2000-01-30')) ==> 6
2000-01-01 is a Saturday, and 2000-01-31 is a Sunday.
(62) YEARS_BETWEEN
(a) Function
Returns as a real number (FLOAT type) the number of years between two dates specified in arguments.
(b) Format
[MASTER.]YEARS_BETWEEN(argument-1, argument-2)
(c) Rules
- The following can be specified in each argument:
- value-expression [AS data-type]
- The following can be specified in each value expression:
- CURRENT DATE
- Column specification
- SQL variable or SQL parameter
- Date operation
- Set function
- Scalar function
- Function call
- CASE expression
- CAST specification
- Embedded variable or ? parameter
- Scalar subquery
- When you specify only an embedded variable or a ? parameter in a value expression, you must specify the AS data type. When the AS data type is specified, items other than an embedded variable or a ? parameter cannot be specified. Specifying the AS data type causes the function that has a parameter with the data type specified in the AS clause to be called.
- The data types of argument-2 and argument-3 must be DATE.
- The data type of the result will be FLOAT.
- The value of the result is not NOT NULL constrained (null values are allowed). If the value expression of argument-1 or argument-2 is the null value, the result is also the null value.
- The number of years which is the result of YEARS_BETWEEN is calculated according to the following rules:
- If date-1 < date-2:
Result = -YEARS_BETWEEN (date-2, date-1)
- If date-1
date-2:
Let y be an integer greater than or equal to 0, and let date-4 and date-3 be the date (date-2 + y years) and (date-2 + (y + 1)) years, respectively, such that date-4
date-1 < date-3
Let d1 and d2 be the number of days between date-4 and date-1 (DAYS (date-1) - DAYS (date-4)) and the number of days between date-4 and date-3 (DAYS (date-3) - DAYS (date-4)), respectively (where d2 is one of the following values: 365, 366)
The number of years in the result will be (y + d1
d2) years.
The following figure shows the relationships between the dates date-1, date-2, date-3, and date-4.
Figure 2-8 Relationships between the dates date-1, date-2, date-3, and date-4
![[Figure]](figure/zu2s0460.gif)
(d) Examples
Examples of the YEARS_BETWEEN function are shown below:
YEARS_BETWEEN(DATE('2000-06-10'),DATE('1999-06-10'))==> 1
YEARS_BETWEEN(DATE('2000-06-11'),DATE('1999-06-10'))==> 1.002739...
YEARS_BETWEEN(DATE('1999-06-11'),DATE('1999-06-10'))==> 1.002732...
YEARS_BETWEEN(DATE('2014-09-09'),DATE('1999-06-10'))==> 15.249315...
YEARS_BETWEEN(DATE('1999-06-10'),DATE('1999-06-10'))==> -15.249315...