DB2: Fun with dates and times

To get current date, time and timestamp using SQL, reference the appropriate registers:

Select current date from sysibm.sysdummy1 ;
Select current time from sysibm.sysdummy1 ;
Select current timestampe from sysibm.sysdummy1 ;

Sysibm.Sysdummy1 is a special in-memory table that can be used to discover the value of DB2 registers as illustrated above.

Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:

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

Extracting the date and time independently from a timestamp is also very easy:

DATE (current timestamp)
TIME (current timestamp)

You can also perform date and time calculations using, for lack of a better term, English:

current date + 1 YEAR
current date + 3 YEARS + 2 MONTHS + 15 DAYS
current time + 5 HOURS - 3 MINUTES + 10 SECONDS

To calculate how many days there are between two dates, you can subtract dates as in the following:

days (current date) - days (date('1999-10-22'))

If you want to concatenate date or time values with other text, you need to convert the value into a character string first. To do this, you can simply use the CHAR() function:

char(current date)
char(current time)
char(current date + 12 hours)

To convert a character string to a date or time value, you can use:

TIMESTAMP ('2002-10-20-12.00.00.000000')
TIMESTAMP ('2002-10-20 12:00:00')
DATE ('2002-10-20')
DATE ('10/20/2002')
TIME ('12:00:00')

The TIMESTAMP(), DATE() and TIME() functions accept several more formats