Determine Elapsed Days, Weekdays, and Workdays
March 31, 2004 Hey, Ted
Finding the number of days between two dates is a common requirement in IT shops. We have a method that works well for us. It lets us include or exclude holidays and weekends.
We have a calendar file that has a record for every date between January 1, 1940, and December 31, 2039. It has quite a few fields, but the three that pertain to this discussion are DATE, DAYOFWEEK, and HOLIDAY. DATE is the date itself, defined with the date data type. DAYOFWEEK is a one-digit packed decimal number in the range 1 (Sunday) to 7 (Saturday), the same values SQL returns through the DAYOFWEEK function. Holiday is also a one-digit packed decimal number that contains a zero (not a holiday) or 1 (holiday).
Here’s the SQL command to find the number of days between two dates.
select count(*) from calendar where date > '12/29/03' and date <= '01/12/04'
Here’s how we find the number of elapsed weekdays.
select count(*) from calendar where dayofweek between 2 and 6 and date > '12/29/03' and date <= '01/12/04'
We can find the number of workdays by omitting holidays and weekends.
select count(*) from calendar where dayofweek between 2 and 6 and holiday = 0 and date > '12/29/03' and date <= '01/12/04'
Good idea, Paul. A calendar file is as handy as a pocket.
Finding the number of days between two dates is easy to do. Here’s another method that doesn’t require a calendar file.
select days('01/12/04') - days('12/29/03') from sysibm/sysdummy1
The DAYS function returns an ordinal value representing the number of days since January 1 of the year 1. The difference between two ordinal values is the date difference. But if you want to omit weekends or holidays, this method won’t work.