Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Date Calculation Excluding Weekend & Holiday Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 23, 2003 9:12 AM
 SSC 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 OutstandingI 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
 Hall of Fame Group: General Forum Members Last Login: Tuesday, August 4, 2015 2:05 AM Points: 3,059, Visits: 36
 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)GOSELECT datediff(day, @creation_date, getdate()) - count(*) AS OutstandingFROM NonBusinessDaysWHERE 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 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 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 ASBEGIN/*Description: Function designed to calculate the number of business days (In hours)between two dates.*/DECLARE @Days int ,@WeekDays int ,@Holidays int ,@Hours intSELECT @Hours = DATEDIFF(Hour,@Start,@End)WHILE (DATEPART(WeekDay,@Start)-1) % 6 = 0BEGIN SELECT @Start = DATEADD(Day,1,@Start) SELECT @Hours = @Hours - 24ENDWHILE (DATEPART(WeekDay,@End)-1) % 6 = 0BEGIN SELECT @End = DATEADD(Day,1,@End) SELECT @Hours = @Hours - 24ENDSELECT @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 *24SELECT @Hours = @WeekDays - @HolidaysRETURN(@Hours)END
Post #84422
 Posted Friday, October 24, 2003 11:27 AM
 SSC Eights! Group: General Forum Members Last Login: Wednesday, September 23, 2009 9:40 AM Points: 939, 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 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 Group: General Forum Members Last Login: Monday, December 15, 2014 7:45 AM Points: 395, Visits: 89
 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.ThanksSreejith
Post #311631
 Posted Wednesday, September 27, 2006 12:13 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 10:52 PM Points: 42,083, Visits: 39,479
 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.aspAlso 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) athttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.htmlAdam 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." Helpful Links:How to post code problemsHow to post performance problems
Post #311644
 Posted Thursday, September 28, 2006 3:48 PM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 11:41 AM Points: 5,676, Visits: 11,187
 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-Forever Group: General Forum Members Last Login: Yesterday @ 10:52 PM Points: 42,083, Visits: 39,479
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #312128

 Permissions