Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Do I get list of Business days between two specific days?


How Do I get list of Business days between two specific days?

Author
Message
Robert Dennyson
Robert Dennyson
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 108
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
tommyh
tommyh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 2000
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;-)
Robert Dennyson
Robert Dennyson
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 108
Hei 100000 thanks to you....Its cool...Yahooooooo
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search