iseries SQL Help Functions and Commands

Iseries / AS400 SQL Built-In Functions

Basic Functions

Function     Description
===============================

MAX         Maximum value from a set of pre-defined values.
MIN         Minimum value from a set of pre-defined values.
AVG         Average value of a set of pre-defined values.
SUM         Sum of a set of pre-defined values.
COUNT         Number of elements in a set of pre-defined values.

Numeric Functions

Function         Description
=======================================

ABS(N)             Absolute value of N.
COS(A) / ACOS(A)
SIN(A) / ASIN(A)
TAN(A) / ATAN(A)     Basic trigonometric functions.
CEILING(N)
FLOOR(N)         Rounding of N to the unit
above/below.
DEGREES(R)         Converts a value in radians to degrees.
RADIANS(D)         Converts a value in degrees to radians.
LN(N)
LOG10(N)         Natural logarithm / base 10 logarithm of N.

String Functions

Function         Description
====================================

CHAR(N)         The string representation of the number N.
CHAR_LENGTH(S)         Length of a string.
CONCAT(S1, S2)         Concatenates S1 with S2.
SUBSTR(S, I, L)     Returns a substring of S, starting at index I of lenght L.
LOWER(S)         Lowercase representation of S.
UPPER(S)         Uppercase representation of S.
TRIM(S)         Removes spaces from the beggining and and of S.
RTRIM(S)        Removes spaces at the begging (right) of S.
LTRIM(S)         Removes spaces at the begging (left) of S.

Date and Time Functions

Function     Description
============================
CURDATE()    System’s current date.
CURTIME()     System’s current time.
DATE(D)     Converts a string representation of a date into into a date value.
DATE(T)     Converts a string representation of a time into into a time value.
DAY(D)         Day(1-31) from the date D.
WEEK(D)     Week (1-54) from the date D.
MONTH(D)     Month (1-12) from the date D.
YEAR(D)     Year from the date D.
DAYOFWEEK(D)     Day (1-7) from the date D where 1 is Sunday.
DAYOFWEEK_ISO(D)Day (1-7) from the date D where 1 is Monday.
DAYOFYEAR(D)     Number of the day, in a year (1-366).
HOUR(T)     Hour (0-24) from the time T.
MINUTE(T)     Minute from the time T.
SECOND(T)     Second from the time T.
MICROSECOND(T)     Microsecond from the time