Weekdays Between Two Dates 1

[BrianTyler]

Has anyone come up with a slick method of counting the number of weekdays (Mon - Fri) between two dates.

It is easy to calculate the whole weeks, by subtraction and division, but dealing with the odd days is messy.

It gets even more complicated because, if the first date is a Saturday we treat it as Monday, and if the second date is a Saturday we treat it as Friday.

 

[sathyarams]

with temp(b) as
(
select date('2004-05-09') from sysibm.sysdummy1
union all
select b+1 day from temp where b < '2004-05-16'
)
select count(*) from
temp where dayofweek_iso(b) between 1 and 5


May be, there are better methods

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[sathyarams]

BTW, I didn't quiet understand

It gets even more complicated because, if the first date is a Saturday we treat it as Monday, and if the second date is a Saturday we treat it as Friday.

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

 

[stevexff]

If you have a start date of Saturday and an end date of Saturday, using a naive algorithm, you get an answer of 5 days.

But if you treat a starting Saturday as a Monday, and the ending Saturday as a Friday, you get an answer of er, 5 days...

I don't really see how this makes it more complicated, as it's irrelevant so you can just ignore it. As a matter of interest, what happens if one of the dates is a Sunday?

 

[BrianTyler]

Thanks for your responses.

The 'confusion' with Saturday dates is because this is a performance monitoring system where respnses to actions are required within so-many working days (Mon-Fri only), but some staff work on Saturdays.

I have decided to do along-winded calculation on the date fields because, due to volumes, I cannot afford a calendar table or sub-queries etc.

I adjust the dates for Saturdays and calculate the number of whole weeks * 5. I then take the daynofweek for each date and subtract lesser from greater, allowing for weekends.