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.
CHARfunction obtains a string representation of a date and time value. See the following syntax:Data types.
X_DATEhas the value May 3, 2000.
X_TIMEhas the value 5 hours, 17 minutes, and 34 seconds.
X_TSTAMPhas the value 5 hours, 17 minutes, and 34 seconds on May 3, 2000.
CHARfunction 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'
DATEfunction obtains a date from a value.
- 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.
X_DATEhas the value April 22, 2023.
X_TSTAMPhas the value 15 hours, 2 minutes, and 1 second on March 6, 2023.
X_STRINGhas the value '2000-03-06'.
DATEfunction 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
DIGITSfunction obtains a character string representation of a number.
DIGITS(754) = '754' DIGITS(00054) = '54' DIGITS(-54) = '54'
INTEGER(45) = 45 INTEGER(-75.3) = -75 INTEGER(0.0005) = 0
INTERVALfunction obtains the length of a time interval in seconds.
TME1has the value of 6 hours, 20 minutes, 29 seconds, and 25000 microseconds.
TME2has the value of 18 hours, 25 minutes, 20 seconds.
DAY1has the value of March 5, 2023.
DAY2has the value of March 8, 2023.
TS1has the value of 5 hours on March 5, 2023.
TS2has the value of 10 hours, 30 minutes on March 11, 2023.
INTERVALfunction produces the following results:
INTERVAL(TME1, TME2) = 43490.975 INTERVAL(TME2, TME1) = -43490.975 INTERVAL(DAY1, DAY2) = 259200.0 INTERVAL(TS1, TS2) = 538200.0
LENGTHfunction obtains the length of a character string.
LENGTHfunction is an integer. Assume that
X_STRINGhas the value of
'LOG_NAME', the function produces the following results:
LENGTH(X_STRING) = 8 LENGTH('REC_LOG') = 7 LENGTH(' ') = 1 LENGTH('') = 0
SUBSTRfunction obtains a substring of a character or bit string.
expression1is 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
C_STRis 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'
B_STRis 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'
TIMESTAMPfunction obtains a timestamp from a value or a pair of values.
- 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.
X_TIMEhas the value 3 hours, 24 minutes, 20 seconds, and 2 microseconds.
X_DATEhas the value February 11, 2023.
X_TSTAMPhas the value 15 hours, 33 minutes, 25 seconds, and 75 microseconds on June 20, 2023.
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
VALUEfunction returns the first argument that is not null.
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.
EXPAhas the value of 25.
EXPBhas the value of 50.
EXPChas a null value.
VALUE(EXPA, EXPB, EXPC) = 25 VALUE(EXPC, EXPB, EXPA) = 50 VALUE(EXPB, EXPA) = 50