• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Meet digital age demands while maximizing your IT investment.

    Future-proof your mission-critical applications with Rocket® Solutions for IBM® i that keep your business ahead of the curve.

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Bankers Will Soon Savor the Java of RIO Paychecks Up Slightly in OS/400 Shops

    Leave a Reply Cancel reply

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle