Date Calculation Excluding Weekend & Holiday

Question

Date Calculation Excluding Weekend & Holiday

CrystalVis

SSC Eights!

Points: 979
More actions
October 23, 2003 at 9:12 am

#83144

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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12535 More actions · Answer 1

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.

CrystalVis SSC Eights! Points: 979 More actions · Answer 2

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 SSC Veteran Points: 229 More actions · Answer 3

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

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

Japjit Kaur SSC Journeyman Points: 79 More actions · Answer 5

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 SSCertifiable Points: 5403 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

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.

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

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

Change SQL Server collation - and it will fail.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 10

That means an application using this function is not really portable...

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

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)

Sergiy SSC Guru Points: 110208 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 13

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)

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 14

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:

http://gallery.technet.microsoft.com/Calculate-Leave-working-501af27a/file/68664/1/02-Calculate%20Leave%20working%20days%20in%20a%20table%20column%20as%20a%20default%20value.sql

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek