Functions

Edit online

A function call is a special form of expression. You can use a function call directly in the statements whenever the syntax specifies an expression. You can also use it as a part of more complex expressions.

CHAR

The CHAR function obtains a string representation of a date and time value. See the following syntax:
Read syntax diagramSkip visual syntax diagram CHAR ( expression )
The argument expression must be a date, a time, or a timestamp. For more information about the date, time, and timestamp values, see Data types.
Assume the following conditions:
  • X_DATE has the value May 3, 2000.
  • X_TIME has the value 5 hours, 17 minutes, and 34 seconds.
  • X_TSTAMP has the value 5 hours, 17 minutes, and 34 seconds on May 3, 2000.
The CHAR function produces the following results:
CHAR(X_DATE) = '2000-05-03'
CHAR(X_TIME) = '05.17.34.000000'
CHAR(X_TSTAMP) = '2000-05-03-05.17.34.000000'

DATE

The DATE function obtains a date from a value.
Read syntax diagramSkip visual syntax diagram DATE ( expression )
The argument expression must be a date, a timestamp, a number, or a date string.
The result of this function is a date.
  • If the argument is a date, the result is that date.
  • If the argument is a timestamp, the result is the date part of that timestamp.
  • If the argument is a number, consider the integer part of that number as n. It must be in the range 1 - 3,652,059. The result of the function is the date of the day with sequential number n, counting from January 1, 0001 as day 1.
  • If the argument is a date string, the result is the date that is represented by that string.
Assume the following conditions:
  • X_DATE has the value April 22, 2023.
  • X_TSTAMP has the value 15 hours, 2 minutes, and 1 second on March 6, 2023.
  • X_STRING has the value '2000-03-06'.
The DATE function produces the following results:
DATE(X_DATE) = April 22, 2023
DATE(X_TSTAMP) = March 6, 2023
DATE(727159) = November 23, 2023
DATE('2000-06-15') = June 15, 2000
DATE(X_STRING) = March 6, 2000

DIGITS

The DIGITS function obtains a character string representation of a number.
Read syntax diagramSkip visual syntax diagram DIGITS ( expression )
The argument expression must be an integer.
The result is the string of digits that represents the absolute value of the argument. Leading zeros are not included in the result.
DIGITS(754) = '754'
DIGITS(00054) = '54'
DIGITS(-54) = '54'

INTEGER

The INTEGER function obtains the integer part of a number.
Read syntax diagramSkip visual syntax diagram INTEGER ( expression )
The argument must be a number.
If the argument is an integer, the result is that integer. If the argument is a floating-point number, the result is the integer part of that number.
INTEGER(45) = 45
INTEGER(-75.3) = -75
INTEGER(0.0005) = 0

INTERVAL

The INTERVAL function obtains the length of a time interval in seconds.
Read syntax diagramSkip visual syntax diagram INTERVAL ( expression , expression )
Both arguments must be date and time values of the same type.
The result is a floating-point number. The result is the interval, in seconds, from the instant designated by the first argument to the instant designated by the second argument. If the first argument is later than the second, the result is negative. The result has the maximum precision that is allowed by its floating-point representation. Therefore, results up to 2283 years have a precision of 1 microsecond. Assume the following conditions:
  • TME1 has the value of 6 hours, 20 minutes, 29 seconds, and 25000 microseconds.
  • TME2 has the value of 18 hours, 25 minutes, 20 seconds.
  • DAY1 has the value of March 5, 2023.
  • DAY2 has the value of March 8, 2023.
  • TS1 has the value of 5 hours on March 5, 2023.
  • TS2 has the value of 10 hours, 30 minutes on March 11, 2023.
The INTERVAL function produces the following results:
INTERVAL(TME1, TME2) = 43490.975
INTERVAL(TME2, TME1) = -43490.975
INTERVAL(DAY1, DAY2) = 259200.0
INTERVAL(TS1, TS2) = 538200.0

LENGTH

The LENGTH function obtains the length of a character string.
Read syntax diagramSkip visual syntax diagram LENGTH ( expression )
The argument must be a character string.
The result of the LENGTH function is an integer. Assume that X_STRING has the value of 'LOG_NAME', the function produces the following results: 
LENGTH(X_STRING) = 8
LENGTH('REC_LOG') = 7
LENGTH(' ') = 1
LENGTH('') = 0

SUBSTR

The SUBSTR function obtains a substring of a character or bit string.
Read syntax diagramSkip visual syntax diagram SUBSTR ( expression1 , expression2 ,expression3 )
The expression1 is called string, and must be a character or bit string. The expression2 is called start, and must be an integer in the range 1 - 254. The expression3 is called length, and must be an integer in the range 0 to 255-expression2.

The result is a character string. If length is specified, the result consists of length bytes of string, starting at the start position. The string is regarded as extended on the right with the necessary number of blanks so that the specified substring exists.

If length is not specified, the result consists of all bytes or bits of string, starting at the position start and extending to the end of string. If start is greater than the length of string, the result is an empty string.

Both start and length are expressed in bytes. The SUBSTR function does not recognize double-byte characters, and the result is not necessarily a well-formed character string.

Assume that C_STR is a character string with the value of 'SUB_REC'. The function produces these results:
SUBSTR(C_STR,3,2) = 'B_'
SUBSTR(C_STR,3) = 'B_REC'
SUBSTR(C_STR,3,10) = 'B_REC'
Assume that B_STR is a 1-byte bit string with the value of '01010111'. The function produces these results:
SUBSTR(B_STR,3,2) = '01'
SUBSTR(B_STR,3) = '010111'
SUBSTR(B_STR,3,10) = '010111'

TIMESTAMP

The TIMESTAMP function obtains a timestamp from a value or a pair of values.
Read syntax diagramSkip visual syntax diagram TIMESTAMP ( expression1 ,expression2 )
The result of the function depends on whether expression1 or expression2 are specified.
If only one argument is specified, expression1 must be a timestamp or a timestamp string. The result is a timestamp:
  • If expression1 is a timestamp, the result is that timestamp.
  • If expression1 is a timestamp string, the result is the timestamp represented by that string.
If both arguments are specified, expression1 must be a date or a date string, and expression2 must be a time or a time string. The result is a timestamp. It consists of the date and time that is specified by the arguments. Assume the following conditions:
  • X_TIME has the value 3 hours, 24 minutes, 20 seconds, and 2 microseconds.
  • X_DATE has the value February 11, 2023.
  • X_TSTAMP has the value 15 hours, 33 minutes, 25 seconds, and 75 microseconds on June 20, 2023.
The function produces the following results:
TIMESTAMP(X_TSTAMP) = 15 hours, 33 minutes, 25 seconds, and 75 microseconds on June 20, 2023
TIMESTAMP('2023-04-17-19.01.25.000000') = 19 hours, 1 minute, 25 seconds on April 17, 2023
TIMESTAMP(X_DATE, X_TIME) = 3 hours, 24 minutes, 20 seconds, and 2 microseconds on February 11, 2023

VALUE

The VALUE function returns the first argument that is not null.
Read syntax diagramSkip visual syntax diagram VALUE ( expression , expression ,expression )
All arguments must have the same data type.

The result has the same data type as the arguments. It is equal to the first argument that is not null. If all arguments are null, the result is null.

Assume the following conditions:
  • EXPA has the value of 25.
  • EXPB has the value of 50.
  • EXPC has a null value.
The function produces the following results:
VALUE(EXPA, EXPB, EXPC) = 25
VALUE(EXPC, EXPB, EXPA) = 50
VALUE(EXPB, EXPA) = 50