• 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

    Software built on TRUST. Delivered with LOVE.

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    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

Volume 4, Number 10 -- March 31, 2004
THIS ISSUE
SPONSORED BY:

Guild Companies
Client Server Development
GST
SuSE Linux
COMMON

Table of Contents

  • Step by Step: RPG IV and Interactive Web Pages
  • Determine Elapsed Days, Weekdays, and Workdays
  • Thinking in Sets
  • Admin Alert: Moving ASCII Data Between IFS and Windows
  • OS/400 Alert: Virus Programming for the Novice

Content archive

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

Recent Posts

  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20
  • 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

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