DB2 Date functions

Part 1

This article describes date functions in db2 .
I think it's more comfortable working with date functions in db2 than in mssql. There are many ways to do more manipulations with dates .

Now I’ll try to write some examples of working with dates .

1) To get the current date , timestamp and time you can write

SELECT current date as MY_DATE FROM sysibm.sysdummy1
SELECT current timestamp FROM sysibm.sysdummy1
SELECT current time FROM sysibm.sysdummy1

The sysibm.sysdummy1 table is a specific table that can be used to discover the value of DB2 registers . Also you can use the VALUES statement For example, from the DB2 Command Line Processor (CLP), the following SQL statements gets similar result:

VALUES current date;
VALUES current timestamp;
VALUES current time ;

2) To get year, month , day , hour , minute , second and microsecond from current timestamp
for example :

VALUES YEAR(current timestamp);
VALUES MONTH(current timestamp);
VALUES DAY(current timestamp);
VALUES HOUR(current timestamp);
VALUES MINUTE(current timestamp);
VALUES SECOND(current timestamp);
VALUES MICROSECOND(current timestamp);

3) To do some calculations between two dates :

current date + 2 YEAR
current date + 4 YEARS + 1 MONTHS + 20 DAYS
current time + 1 HOURS - 2 MINUTES + 3 SECONDS

4) To calculate difference between two dates you can do like that

days (current date) - days (date('1986-06-16'))


5) If you want to concatenate date or timestamp with text you can do like that

char(current timestamp)
char(current time)
char(current date + 2 hours)