## Date Calculation Excluding Weekend & Holiday

 Author Message CrystalVis Old Hand Group: General Forum Members Points: 363 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 NPeeters SSCertifiable Group: General Forum Members Points: 6026 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. CrystalVis Old Hand Group: General Forum Members Points: 363 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 KBusley Valued Member Group: General Forum Members Points: 63 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 Jonathan SSCertifiable Group: General Forum Members Points: 5887 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 Japjit Kaur Valued Member Group: General Forum Members Points: 57 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. Sreejith Sreedharan SSCrazy Group: General Forum Members Points: 2931 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 Jeff Moden SSC Guru Group: General Forum Members Points: 505994 Visits: 44260 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Sergiy SSC Guru Group: General Forum Members Points: 59010 Visits: 12969 Yes, that function is OK, but only for English speaking servers.Change SQL Server collation - and it will fail. Jeff Moden SSC Guru Group: General Forum Members Points: 505994 Visits: 44260 Then, simply change the English days of the week to something the non-English speaking server understands. --Jeff ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs