Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How Do I get list of Business days between two specific days? Expand / Collapse
Author
Message
Posted Thursday, September 02, 2010 4:10 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 06, 2012 5:27 AM
Points: 13, 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
Post #979388
Posted Thursday, September 02, 2010 4:36 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
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
Post #979400
Posted Thursday, September 02, 2010 5:24 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 06, 2012 5:27 AM
Points: 13, Visits: 108
Hei 100000 thanks to you....Its cool...Yahooooooo
Post #979432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse