Datetime Conversions: DB2

DB2 recently added a great deal of support to emulate Oracle’s TO_CHAR and TO_DATE functions. If compatibility with Oracle is important, test to see whether the functions described under Datetime Conversions: Oracle will work for you.

Otherwise, use the following functions to convert to and from dates, times, and timestamps. In the syntax, datetime can be a date, time, or timestamp; date must be either a date or a timestamp; time must be either a time or a timestamp; and timestamp must be a timestamp. Similarly, dateduration must be a date or timestamp duration; timeduration must be either a time or timestamp duration; and timestampduration must be a timestamp duration. Valid string representations of all of these types are allowed as well:

BIGINT(datetime)
CHAR(datetime, [ISO|USA|EUR|JIS|LOCAL])
DATE(date)
DATE(integer)
DATE('yyyyddd')
DAY(date)
DAY(dateduration)
DAYNAME(date)
DAYOFWEEK(date)
DAYOFWEEK_ISO(date)
DAYOFYEAR(date)
DAYS(date)
DECIMAL(datetime[,precision[,scale]])
GRAPHIC(datetime, [ISO|USA|EUR|JIS|LOCAL])
HOUR(time)
HOUR(timeduration)
INTEGER(date_only)
INTEGER(time_only)
JULIAN_DAY(date)
MICROSECOND(timestamp)
MICROSECOND(timestampduration)
MIDNIGHT_SECONDS(time)
MINUTE(time)
MINUTE(timeduration)
MONTH(date)
MONTH(dateduration)
MONTHNAME(date)
QUARTER(date)
SECOND(time)
SECOND(timeduration)
TIME(time)
TIMESTAMP(timestamp)
TIMESTAMP(date, time)
TIMESTAMP_FORMAT(string, 'YYYY-MM-DD HH24:MI:SS')
TIMESTAMP_ISO(datetime)
TO_CHAR(timestamp, 'YYYY-MM-DD ...

Get SQL Pocket Guide, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.