Advanced use of DB2 date functions

DB2 date functions are one of the DB2 functions that we are very familiar with. Next we will introduce you to some advanced usage methods of DB2 date functions, and hope to help you learn about DB2 date functions.

Sometimes, you need to know the time difference between two timestamps. Therefore, DB2 provides a built-in function named TIMESTAMPDIFF. However, this function returns an approximate value because it does not consider a leap year and assumes that there are only 30 days each month. The following example describes how to obtain the approximate time difference between two dates:

Timestampdiff (<n>, char (
Timestamp ('2017-11-30-00.00.00 ')-
Timestamp ('2017-11-08-00.00.00 ')))

For <n>, you can use the following values to indicate the time unit of the result:

1 = decimal part of second
2 = seconds
4 = minute
8 = hour
16 = days
32 = week
64 = month
128 = quarter
256 = year

Use timestampdiff () when the date is very close, which is more accurate than the date difference. If you need more accurate calculation, you can use the following method to determine the time difference by second ):

(DAYS (t1)-DAYS (t2) * 86400 +
(MIDNIGHT_SECONDS (t1)-MIDNIGHT_SECONDS (t2 ))

For convenience, you can also create SQL user-defined functions in the preceding method:

Create function secondsdiff (t1 TIMESTAMP, t2 TIMESTAMP)
RETURNS INT
RETURN (
(DAYS (t1)-DAYS (t2) * 86400 +
(MIDNIGHT_SECONDS (t1)-MIDNIGHT_SECONDS (t2 ))
)
@

To determine whether a given year is a leap year, you can create an SQL function to determine the number of days in a given year:
Create function daysinyear (yr INT)
RETURNS INT
RETURN (CASE (mod (yr, 400) WHEN 0 THEN 366 ELSE
CASE (mod (yr, 4) WHEN 0 THEN
CASE (mod (yr, 100) WHEN 0 THEN 365 ELSE 366 END
ELSE 365 END
END )@

Finally, the following is a built-in function table for date operations. It is designed to help you quickly identify functions that may meet your requirements, but does not provide a complete reference.
For more information about these functions, see SQL Reference.

SQL date and time functions:

DAYNAME: returns a string of both upper and lower cases. For the day part of the parameter, the name of the day is represented by week, for example, Friday ).
DAYOFWEEK: return the day of the week in the parameter, expressed by an integer ranging from 1 to 7, where 1 represents Sunday.
DAYOFWEEK: _ ISO returns the day of the week in the parameter, expressed by an integer ranging from 1 to 7, where 1 represents Monday.
DAYOFYEAR: returns the day of the year in the parameter, expressed by an integer ranging from 1 to 366.
DAYS: return an integer of the date.
JULIAN_DAY: returns the number of days from the start date of the calendar in June January 1, 4712 BC to the specified date value in the parameter, expressed in an integer.
MIDNIGHT_SECONDS: returns the number of seconds between midnight and the specified time value in the parameter, which is expressed by an integer ranging from 0 to 86400.
MONTHNAME: For the month part of the parameter, return a string of both upper and lower cases, for example, January ).
TIMESTAMP_ISO: returns a time stamp Value Based on the date, time, or time stamp parameter.
TIMESTAMP_FORMAT: returns a timestamp from a string interpreted using a character template.
TIMESTAMPDIFF: returns the estimated time difference expressed by the type defined by the first parameter based on the time difference between two timestamps.
TO_CHAR: returns the time stamp string representation formatted using the character template. TO_CHAR: a synonym for VARCHAR_FORMAT.
TO_DATE: returns a timestamp from a string that has been interpreted using a character template. TO_DATE is a synonym for TIMESTAMP_FORMAT.
WEEK: return the WEEK of the year in the parameter, expressed by an integer ranging from 1 to 54. Start from Sunday.
WEEK_ISO: return the week of the year in the parameter, expressed by an integer ranging from 1 to 53.
 

Tips for DB2 data deletion

Introduction to DB2 SQL Execution Plan Monitoring

Learn about DB2 snapshot monitoring

How to Implement online DB2 Import

Common db2 stored procedure statements