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 12»»

Date Calculation Excluding Weekend & Holiday Expand / Collapse
Author
Message
Posted Thursday, October 23, 2003 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 1:17 PM
Points: 33, Visits: 86
I need to be able to find out the number of days between two date excluding weekend and holidays. For example:
Datediff(day,creation_date,getdate())as Outstanding
I want to find the number of days excluding holidays and weekend. Your help/suggestion is greatly appreciated.
Thanks





Post #17545
Posted Thursday, October 23, 2003 9:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 19, 2014 2:58 AM
Points: 2,642, Visits: 31
To exclude weekends, there is a 'simple' solution doing some simple 'date'math. This has been solved before on these forums...

To also exclude holidays is a lot trickier. The only 'easy' solution I can see is to build a table that holds all dates that should be excluded.

The statement would be something like

CREATE TABLE NonBusinessDays
(NBDDate smalldatetime)
GO

SELECT datediff(day, @creation_date, getdate()) - count(*) AS Outstanding
FROM NonBusinessDays
WHERE NBDDate >= @creation_date and NBDDate < getdate()

Either join to your source table or pass in Creation_date as a parameter.



Post #84420
Posted Thursday, October 23, 2003 12:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 1:17 PM
Points: 33, Visits: 86
NPeeters,
Can you please elaborate on what is going on in the sample code you post. Especially the part - count(*). What purpose is this serving.
This is how it was done in Access. However, I don't think there is a similar function in T-SQL.
Days in Error: workdays([creation_date],Date())

Thanks




Post #84421
Posted Friday, October 24, 2003 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 2, 2003 12:00 AM
Points: 1, Visits: 1
Here's a user defined function that does what you need. You will have to create tblHolidays and populate it with all the holidays you want excluded.

CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime)
RETURNS int
AS
BEGIN
/*
Description:
Function designed to calculate the number of business days (In hours)
between two dates.
*/
DECLARE
@Days int
,@WeekDays int
,@Holidays int
,@Hours int


SELECT @Hours = DATEDIFF(Hour,@Start,@End)
WHILE (DATEPART(WeekDay,@Start)-1) % 6 = 0
BEGIN
SELECT @Start = DATEADD(Day,1,@Start)
SELECT @Hours = @Hours - 24
END
WHILE (DATEPART(WeekDay,@End)-1) % 6 = 0
BEGIN
SELECT @End = DATEADD(Day,1,@End)
SELECT @Hours = @Hours - 24
END

SELECT @WeekDays = @Hours -ABS(DATEDIFF(Week,@End,@Start) * 48)


SELECT @Holidays = COUNT(*) FROM tblHolidays WHERE (HolidayDate BETWEEN @Start AND @End)
AND DATEPART(Weekday,HolidayDate)-1 % 6 <> 0 *24

SELECT @Hours = @WeekDays - @Holidays
RETURN(@Hours)

END



Post #84422
Posted Friday, October 24, 2003 11:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, September 23, 2009 9:40 AM
Points: 940, Visits: 54
quote:

Here's a user defined function that does what you need. You will have to create tblHolidays and populate it with all the holidays you want excluded.

sp_helplanguage


As you cannot use SET DATEFIRST in a UDF, you may want to revise the DOW logic to work with @@DATEFIRST...

--Jonathan




--Jonathan
Post #84423
Posted Wednesday, September 27, 2006 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 19, 2007 11:11 AM
Points: 1, Visits: 1
This function works for calculating business hours between two dates, does anyone has a function for calculating number of business days between two dates excluding holidays too.
Post #311561
Posted Wednesday, September 27, 2006 11:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 1:57 PM
Points: 394, Visits: 83

why can't u use the post by NPeeters. Thats how u should be doing."Holidays" vary by divsion by company by location. So having a holiday table would be the best way to handle any situation.

Thanks

Sreejith

Post #311631
Posted Wednesday, September 27, 2006 12:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222

As suggested, do a search on this forum... there's a particullarly good and nasty fast function to calculate Work Days (week days, actually) at http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp

Also as suggested, you will need to incorporate a "Holiday Table"... you can find out how to do that (and more with calendar tables and the like) at

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Adam Mechanic was the guy who turned me on to Tally or Numbers tables.  Haven't needed a full blown calendar table but have made a holidy table.  It's definitely worth the read... please ignore the fact that he used an (ungh! ) cursor for one thing... he must'a been feeling poorly that day



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #311644
Posted Thursday, September 28, 2006 3:48 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341

Yes, that function is OK, but only for English speaking servers.

Change SQL Server collation - and it will fail.

Post #312095
Posted Thursday, September 28, 2006 6:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:30 PM
Points: 36,766, Visits: 31,222
Then, simply change the English days of the week to something the non-English speaking server understands.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #312128
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse