sql working days holidays

Question

I've seen different version of this kind of function for other coding languages (Python, jQuery, etc.) but not for SQL. I have a procedure that needs to have a date calculated that is 65 days from the creation date, but it cannot include weekend or holidays. We already have a function that is able to add only working days to a date, but not take into account holidays. We have a holiday table that lists all the holiday dates, tblHolidayDates with a column HolidayDate in standard date format.

How would I do this? I'd also consider maybe just creating a Calendar table as well if someone could give me a CREATE TABLE query for that - all it would need is dates, weekday, and holiday columns.

Below I have given the current loop function that adds business days, but it's missing holidays. Any help would be greatly appreciated!!

ALTER FUNCTION [dbo].[AddWorkDaysToDate]
(   
@fromDate       datetime,
@daysToAdd      int
)
RETURNS datetime
AS
BEGIN   
DECLARE @toDate datetime
DECLARE @daysAdded integer

-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate

while @daysAdded <= @daysToAdd
begin
-- add a day to the to date
set @toDate = DateAdd(day, 1, @toDate)
-- only move on a day if we've hit a week day
if (DatePart(dw, @toDate) != 1) and (DatePart(dw, @toDate) != 7)
begin
    set @daysAdded = @daysAdded + 1
end
end

RETURN @toDate

END

I removed the MySQL tag because the question uses SQL Server syntax. — Gordon Linoff, Dec 15 '13 at 15:07
If you decide to create a calendar table, you should drop your holidays table. That way you only have one set of data to maintain. You would not need a weekday column because you can use datepart() to get that. You may find a need to add fiscal year/period/quarter fields later on. — Dan Bracuk, Dec 15 '13 at 16:36

Sparky · Answer 1 · 2013-12-16T19:56:42.433

3

I wrote a SQL function years ago to build a dynamic holiday table as a table function. The link is below:

http://www.joebooth-consulting.com/sqlServer/sqlServer.html#CalendFunc

Hope it helps...

You can access the table function (or your own holiday table) to determine number of holidays via a SQL statement like below

SELECT count(*) FROM holiday_date(2013)
WHERE holiday_date BETWEEN @fromDate AND @toDate

then add the count to the returned date using dateAdd().

If you might have holidays that fall on a weekend, add the following to the WHERE clause

   AND DatePart(dw, Holiday_date) != 1) and (DatePart(dw, holiday_date) != 7)

edited Dec 16 '13 at 19:56

answered Dec 15 '13 at 15:14

Sparky

14,967
2
31
45

This is brilliant! Helped a lot with a project I'm working on, being able to use custom holidays! – Mike Aug 31 '17 at 19:39
Glad it helped Mike, always felt better way to handle holidays. – Sparky Sep 01 '17 at 12:05

score 1 · Answer 2 · answered Dec 15 '13 at 15:10

1

I've always achieved this with a static table of dates from roughly 5 years in the past to 10 in the future, each date being marked with 'working day' status and sometimes other flags as required. Previously using MS-SL server I would achieve this quickly with a WHILE loop, I think MySQL supports the same syntax

WHILE (condition)
BEGIN
 INSERT date
END

To create the table either use the Enterprise Manager UI or something like

CREATE TABLE DateTable
 (
   actual_date datetime NOT NULL,
   is_holiday bit NOT NULL
 )

answered Dec 15 '13 at 15:10

Dave

355
2
9

A datetime datatype for a table of dates is inappropriate. The OP is using sql server which has a date datatype available. Also, if you are going to store holiday info, storing the holiday name is much more useful than a bit function. Minus 1 from me. – Dan Bracuk Dec 15 '13 at 16:39
A lot will depend of usage of course, but I considered that the original post wasn't flagged as MS SQL Server, and certainly had no version attached: pre 2008 there was no separate 'date' data type, and I'm not sure if all versions of MySQL support that type. For most usage scenarios, the difference in storage allocation won't be prohibitive but you are certainly correct, for MS SQL Sever 2008 and later, Date is a better choice. The question is about creating a calendar table, which needs to indicate working days, not what label they have, so I think a bit flag is more use than a label? – Dave Dec 15 '13 at 20:39

Daniel B · Answer 3 · 2015-05-13T07:02:38.537

0

Assuming we have a DateTable with columns actual_date (date) and is_holiday (bit) , containing all dates and where all workdays have is_holiday=0:

SELECT actual_date from 
(
    SELECT actual_date, ROW_NUMBER() OVER(ORDER BY actual_date) AS Row 
    FROM DateTable
    WHERE is_holiday= 0 and actual_date > '2013-12-01'
) X
WHERE row = 65

This will find 2013-12-01 plus 65 workdays, skipping holidays.

edited May 13 '15 at 07:02

answered Dec 15 '13 at 15:40

Daniel B

797
4
13

Nice idea but does not seem to work (The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.) – Steve May 12 '15 at 15:15
@Steve - You are right, but the ORDER BY is only necessary (and allowed) in the OVER() clause. After removing the second ORDER BY the query now executes correctly in SQL Server. – Daniel B May 13 '15 at 07:05

sql working days holidays

3 Answers3

Linked