This short article is intended for those who are new to DB2 and wish to understand how to manipulate dates and times.
For example, from the DB2 Command Line Processor (CLP), the following SQL statements reveal similar information: 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.
Warning: What happens if you accidentally leave out the quotes in the DATE function?
The function still works, but the result is not correct: Why the 2,000 year difference in the above results?
When the DATE function gets a character string as input, it assumes that it is valid character representation of a DB2 date, and converts it accordingly.
By contrast, when the input is numeric, the function assumes that it represents the number of days minus one from the start of the current era (that is, 0001-01-01).In the above query the input was 2001-09-22, which equals (2001-9)-22, which equals 1970 days.Sometimes, you need to know how the difference between two timestamps.For this, DB2 provides a built in function called TIMESTAMPDIFF().The value returned is an approximation, however, because it does not account for leap years and assumes only 30 days per month.Here is an example of how to find the approximate difference in time between two dates: Using timestampdiff() is more accurate when the dates are close together than when they are far apart.