Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


calculating Business days

calculating Business days

calculating Business days

  Given a date range I need to calculate the no. of business days in it.Can any one help me with the sql query that i should use in QMF.


RE: calculating Business days

Are you just excluding weekends, or are you also excluding holidays?

RE: calculating Business days

Hi ddiamond,

 I want to exclude the weekends only.


RE: calculating Business days


SELECT count(*)
FROM myTable
WHERE dt BETWEEN StartDate AND EndDate

RE: calculating Business days

Your start date in end date must be either actual dates, or strings in the format 'yyyy-mm-dd'.

RE: calculating Business days

    when DAYOFWEEK_ISO(StartDate)<3
    then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2
    when DAYOFWEEK_ISO(StartDate)=3
    then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2-1
    when DAYOFWEEK_ISO(StartDate)>3
    then days(enddate)-days(startdate)-floor((days(enddate)-days(startdate))/7)*2-2
    end as BusDays
from myTable

RE: calculating Business days

never mind, my 2nd solution doesn't work in all cases.

RE: calculating Business days


  what are the cases and what is the logic for taking
3 as the condition.......and of doing 2 the 2-1
then 2-1.Please reply

RE: calculating Business days

"If startdate is either monday or tuesday then take the number of days between startdate and enddate and correct for the number of days that belong to the weekends in the period between start and enddate."

That is you should read the first part of the case statement.
You will probably need to extend the logic to get all cases covered. I'd say the first solution offered is pretty neat and simple!

Ties Blom

RE: calculating Business days

Ties is correct.  The cases I listed above are correct, but I am missing some.  When I saw that the number of special cases was growing, I abandoned that approach.  I believe I have covered all of the special cases for date ranges of 7 days or more.  I discovered you have an additional set of special cases when the date span is less than 7 days.

RE: calculating Business days

Hi all,
I don't think that Dan's first example works as it counts rows in the table rather than the days between the dates.

In order to do this you need to know both the start day of the week and the end day of the week, so that you can adjust the business days accordingly . Like Dan, I played around with subtracting one date from another giving the number of days, and then dividing by 7 in order to giving the number of weeks. I toyed with both CEIL and FLOOR but couldn't get round the problem that you need to adjust for both start and end. When the CASE statement I had, needed to go down two levels, I decided it was getting too complicated to maintain easily.

I am therefore of the opinion that it's probably too complicated to do easily in straight SQL. I'm not saying it can't be done, just that whatever is produced would be so complicated as to be non-maintainable.

Page 387 of Graeme Birchall's cookbook http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V82CK.PDF creates a user function as follows:

Business Day Calculation
The following function will calculate the number of business days (i.e. Monday to Friday) between to two dates:

CREATE FUNCTION business_days (lo_date DATE, hi_date DATE)
   DECLARE cur_date DATE;
   SET cur_date = lo_date;
   WHILE cur_date < hi_date DO
      IF DAYOFWEEK(cur_date) IN (2,3,4,5,6) THEN
         SET bus_days = bus_days + 1;
      END IF;
      SET cur_date = cur_date + 1 DAY;
   RETURN bus_days;

Important – This example uses an “!” as the stmt delimiter.

Below is an example of the function in use:
WITH temp1 (ld, hd) AS
   (VALUES (DATE('2006-01-10'),DATE('2007-01-01'))
      ,DAYS(hd) - DAYS(ld) AS diff
      ,business_days(ld,hd) AS bdays
FROM temp1 t1;


RE: calculating Business days

Good point Marc.  I believe there should be a simple mathematical formula for this, but I have not come up with one.

RE: calculating Business days

Just in case you want SQL:

Assuming you want number of weekdays betwwn 1st Jan 2006 and 1st May 2006

with temp(day1) as
select date('2006-01-01') from sysibm.sysdummy1
union all
select day1+1 day from temp where day1<=date('2006-05-01')
select count(*) from temp
where  dayofweek(day1) between 2 and 6



For db2 resoruces visit www.db2click.com
More DB2 questions answered at www.dbforums.com/f8 & http://www.idug.org/user/UserLogin.asp

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close