September 2, 2010 at 4:10 am
How Do I get list of Business days between two specific days?
I should get Date like this
'9/2/2010' to '9/10/2010'
Date should omit the saturdays and sundays from the result
September 2, 2010 at 4:36 am
Robert Dennyson (9/2/2010)
How Do I get list of Business days between two specific days?I should get Date like this
'9/2/2010' to '9/10/2010'
Date should omit the saturdays and sundays from the result
Using a tally table (like http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/)
You could do something like this
declare @Start datetime
declare @End datetime
select @Start = '2010-08-01', @End = '2010-08-31'
select DateAdd(dd, N-1, @Start)
from Tally
where N <= DateDiff(dd, @Start, @End) + 1
and DatePart(dw, DateAdd(dd, N-1, @Start)) not in (1,7)
Though this only works if your week starts on Sunday, check @@DATEFIRST. If that doesnt say 7 then you will have to change the "not in (1,7)" part to match your days.
And you should be aware that this doesnt work for holidays (like Christmas, which normally isnt Business days... well depends on where you live i guess :-)). To remove other days as well... thats a TOTALLY different issue and much harder to solve.
Running the code gives me
2010-08-02 00:00:00.000
2010-08-03 00:00:00.000
2010-08-04 00:00:00.000
2010-08-05 00:00:00.000
2010-08-06 00:00:00.000
2010-08-09 00:00:00.000
2010-08-10 00:00:00.000
2010-08-11 00:00:00.000
2010-08-12 00:00:00.000
2010-08-13 00:00:00.000
2010-08-16 00:00:00.000
2010-08-17 00:00:00.000
2010-08-18 00:00:00.000
2010-08-19 00:00:00.000
2010-08-20 00:00:00.000
2010-08-23 00:00:00.000
2010-08-24 00:00:00.000
2010-08-25 00:00:00.000
2010-08-26 00:00:00.000
2010-08-27 00:00:00.000
2010-08-30 00:00:00.000
2010-08-31 00:00:00.000
Oh and this is seriously in the wrong forum;-)
September 2, 2010 at 5:24 am
Hei 100000 thanks to you....Its cool...Yahooooooo
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply