Here’re examples to get the current date, time, and timestamp using SQL which is a reference for corresponding DB2 registers.
How to Use Date and Time in DB2
SELECT CURRENT DATE FROM sysibm.sysdummy1; SELECT CURRENT TIME FROM sysibm.sysdummy1; SELECT CURRENT timestamp FROM sysibm.sysdummy1;
The sysibm.sysdummy1 table is a unique in-memory table to discover the value of DB2 registers as illustrated above. Use the VALUES keyword to evaluate the registry (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 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)
Pulling the date and time independently from a timestamp: Here’s a way:
- 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 to reset to zero:
CURRENT TIMESTAMP – MICROSECOND (current timestamp) MICROSECONDS
Also read: DB2 Enhancements on SQL
If you want to concatenate date or time values with other text, convert the value into a character string first. To do this, you can use the CHAR() function:
- CHAR(CURRENT date)
- char(CURRENT time)
- char(CURRENT date + 12 hours)
Here’s a way to convert a character string to a date or time
Timestamp (‘2002-10-20 12:00:00’ )
The TIMESTAMP(), DATE(), and TIME() functions accept several more formats. The above list is an example. There are many more to try.
give me a programe for example
I have given sample program.
Thanks for complement.
Comments are closed.