Date Calculation Excluding Weekend & Holiday

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Date Calculation Excluding Weekend & Holiday

19 posts, Page 1 of 2

1 2 »»

Date Calculation Excluding Weekend & Holiday

Rate Topic

Display Mode

Topic Options

Author

Message

CrystalVis

Posted Thursday, October 23, 2003 9:12 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 1:16 PM
Points: 33, Visits: 78

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

NPeeters

Posted Thursday, October 23, 2003 9:24 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, April 04, 2013 1:58 AM
Points: 2,480, Visits: 27

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

CrystalVis

Posted Thursday, October 23, 2003 12:13 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 1:16 PM
Points: 33, Visits: 78

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

KBusley

Posted Friday, October 24, 2003 10:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 02, 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

Jonathan

Posted Friday, October 24, 2003 11:27 AM

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

Japjit Kaur

Posted Wednesday, September 27, 2006 8:49 AM

Forum 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

Sreejith Sreedharan

Posted Wednesday, September 27, 2006 11:39 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:41 PM
Points: 392, Visits: 72

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

Jeff Moden

Posted Wednesday, September 27, 2006 12:13 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 33,111, Visits: 27,037

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #311644