• 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
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions.

    Upgrading to Power10, Power11, or cloud hosted system, Maxava has you covered!

    Book A Consultation Today

    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

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

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