To find the no of holidays between two date

Question

Post reply

To find the no of holidays between two date

Chaitra

SSC Enthusiast

Points: 189
More actions
August 12, 2007 at 10:21 pm

#91220

hi,
I have two date colums startdate and releaseddate. I want to display the difference of these two dates. But i should deduct the holidays if any in between those dates. Like sunday, 2nd saturday or any company specified holiday. how to find out the no of holidays between the two given date?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi there,

What you could do is create a table that holds all the holidays for you company(don't need to include week-ends).

Then you can right a query that does a dateDiff minus the number of dates in your table between those dates and minus the week-ends.

thank this make sense?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I agree with Christopher's suggestion. I'd create a table of holidays because these can change from year to year for a company. This way you can join in this table and could the number of holidays between two dates.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Here is an article about that

http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

And, here's a simple script for calculating the number of Weekdays between two given dates. Does not include exclusions for holidays... you would need a holiday table for that.

http://www.sqlservercentral.com/columnists/jmoden/calculatingworkdays.asp

--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)

Chaitra SSC Enthusiast Points: 189 More actions · Answer 5

Thank you peter and Jeff for your articles. They really are very helpful. But still I have some problems . Hope you dont mind to answer my silly questions.

the following is the query i have written to deduct the week ends from the dates(highlight). But not getting how deduct the holidays. I have stored holidays in the table D_HOLIDAYS with the column HDATE.

And also is it possible to deduct only 2nd and 4th saturday instead of all saturdays?

SELECT pno, task, revision, reassign, hold, enq_rec_date, assig_date, str_date, rel_date, exp_date,hld_Date,unhld_date,

DATEDIFF(dd, str_date, rel_date) + 1 AS RELSTR, DATEDIFF(dd, hld_date, unhld_date)+1 as HLDUNHLD,

DATEDIFF(dd, str_date, rel_date) + 1-(DATEDIFF(dd, hld_date, unhld_date)-(DATEDIFF(wk, hld_Date, unhld_Date) * 2)-(CASE WHEN DATENAME(dw, hld_Date) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, unhld_Date) = 'Saturday' THEN 1 ELSE 0 END))-(DATEDIFF(wk, Str_Date, rel_Date) * 2)-(CASE WHEN DATENAME(dw, Str_Date) = 'Sunday' THEN 1 ELSE 0 END)-(CASE WHEN DATENAME(dw, rel_Date) = 'Saturday' THEN 1 ELSE 0 END) AS Newholiday,

DATEDIFF(dd, assig_date, rel_date) + 1 AS RELASS, DATEDIFF(dd, enq_rec_date, rel_date) + 1 AS RELENQ

FROM dbo.D_task_det

WHERE (status = 'Released' OR

status = 'Revised') AND (revision = '0') AND (pno LIKE 'P[1-9]%')