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'       

–Paul

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.

–Ted