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
- How to get Current Date, Current Time and Current Timezone in DB2
- How to Add month, day, and Year to current_date
- DB2 DATE advanced functions
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
How to get Current Date, Current Time and Current Timezone in DB2
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;
How to Add month, day, and Year to current_date
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.
DB2 DATE advanced functions
Below are the advanced DB2 Date functions. Those work similarly to Oracle functions.
ROUND, TRUNC, ADD_MONTHS, LAST_DAY, NEXT_DAY, and MONTHS_BETWEEN.
SET :RND_DATE = ROUND(DATE('2000-08-16'), 'MONTH');
SELECT TRUNC(col_dt, 'YEAR') FROM mytab;
SET :ADD_MONTH = ADD_MONTHS(LAST_DAY(CURRENT_DATE), 1 )
SET :NEXTDAY = NEXT_DAY(TIMESTAMP '2007-04-24-00.00.00.000000', 'TUESDAY');
SELECT MONTHS_BETWEEN ('2008-02-20','2008-01-17') AS MONTHS_BETWEEN FROM SYSIBM.SYSDUMMY1;
You must be logged in to post a comment.