DB2 DATE and Time Conversion: Top SQL Queries

Here are SQL examples to format Date and Time in DB2 using to-date and to-char functions.

Table of contents

How to use to_char and to_date

Here are two functions( TO_CHAR and TO_DATE) SQL developers use to format Date and Time.

SELECT TO_CHAR(TIMESTAMP_COLUMN, 'YYYY-MM-DD HH24:MI:SS');

The TO_CHAR function converts the timestamp to the format you have specified here. It’s the same as the Oracle function. Here are the data types for IBM DB2 DATE and TIME.

SELECT TO_DATE(COLUMN_NAME,  'YYYY-MM-DD');

The TO_DATE function converts the string into the desired format. It’s the same as the Oracle function. Here are IBM-supported Date format strings.

SELECT TO_CHAR (begin_date, '%A %B %d, %Y %R') FROM tab1;
Output
Wednesday July 25, 2013 18:45

In DB2, you can use the SYSDUMMY1 table to get all the current values of DATE, TIME, and TIME ZONE.

SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT_TIME FROM SYSIBM.SYSDUMMY1;
SELECT CURRENT_TIMEZONE FROM SYSIBM.SYSDUMMY1;

Below, you will find an example that you can use to calculate Dates.

CURRENT_DATE + 1 YEARS - 3 MONTHS  + 10 DAYS

The supported labels are YEAR, YEARS, MONTH, MONTHS, DAY, DAYS, HOUR, HOURS, MINUTE, MINUTES, SECOND, SECONDS, MICROSECOND, and MICROSECONDS.

Below are the advanced DB2 Date functions. Those work similarly to Oracle functions.

ROUND, TRUNC, ADD_MONTHS, LAST_DAY, NEXT_DAY, and MONTHS_BETWEEN.

ROUND

SET :RND_DATE = ROUND(DATE('2000-08-16'), 'MONTH');

TRUNC

SELECT TRUNC(col_dt, 'YEAR') FROM mytab;

ADD_MONTHS

SET :ADD_MONTH = ADD_MONTHS(LAST_DAY(CURRENT_DATE), 1 )

NEX_DAY

SET :NEXTDAY = NEXT_DAY(TIMESTAMP '2007-04-24-00.00.00.000000', 'TUESDAY');

MONTHS_BETWEEN

SELECT MONTHS_BETWEEN ('2008-02-20','2008-01-17') 
       AS MONTHS_BETWEEN 
       FROM SYSIBM.SYSDUMMY1;

References