DB2- Date, Time, Timestamp

The basics

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

SELECT current date FROM sysibm.sysdummy1

SELECT current time FROM sysibm.sysdummy1

SELECT current timestamp FROM sysibm.sysdummy1.

The sysibm.sysdummy1 table is a special in-memory table that can be used to discover the value of DB2 registers as illustrated above. You can also use the VALUES keyword to evaluate the register or expression. For example, from the DB2 Command Line Processor (CLP), the following SQL statements reveal similar information:

VALUES current date 

VALUES current time 

VALUES current timestamp

For the remaining examples, I will simply provide the function or expression without repeating SELECT ... FROM sysibm.sysdummy1 or using the VALUES clause.

To get the current time or current timestamp adjusted to GMT/CUT, subtract the current timezone register from the current time or timestamp:

current time - current timezone 

current timestamp - current timezone

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'))

And here is an example of how to get the current timestamp with the microseconds portion reset to zero:

CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS

Also read: DB2 Enhancements on SQL and other

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') 

TIME ('12.00.00')

The TIMESTAMP(), DATE() and TIME() functions accept several more formats. The above formats are examples only and I’ll leave it as an exercise for the reader to discover them.


Keep Reading

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.