Date Calculation Excluding Weekend & Holiday
-
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
-
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.
-
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
-
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
-
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_helplanguageAs you cannot use SET DATEFIRST in a UDF, you may want to revise the DOW logic to work with @@DATEFIRST...
--Jonathan
--Jonathan -
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.
-
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
-
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) at
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, that function is OK, but only for English speaking servers.
Change SQL Server collation - and it will fail.
_____________
Code for TallyGenerator -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That means an application using this function is not really portable...
_____________
Code for TallyGenerator -
It is if you make it table driven for the days of the week based on language...
But I get your point... to be portable, you would have to use one of those "trick" date-first calculations that would make up for whatever DATEFIRST is set to without actually setting date first...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Nothing is tricky here.
(datepart(dw, @Date)+ @@datefirst)%7 is constant and does not depend on datefirst.
By adding a parameter:
(datepart(dw, @Date)+ @@datefirst+6-@N)%7+1
you may set up any datefirst you want without changing database settings.
If you set @N = 1 it will return 1 for Monday, 2 for Tuesday.
Set @N = 7 and you'll get 2 for Monday, 3 for Tuesday.
_____________
Code for TallyGenerator -
That's why "trick" was in quotes
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I knw its very old thread but leaving this query link for someone who needs it still now
Calculate Leave working days in a table column as a default value--updated
If you are using SQL here is the query which can help you:
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply