Share via

DATEADD excluding weekends and holidays

  • Article
  • 03/17/2015

Question

Tuesday, March 17, 2015 5:52 PM

select getdate(),dateadd(day,15,Getdate())

THis gets me today and 15 days from now. However I need 15 business days. I need to exclude weekends and holidays in here so that is looks like this. 

where fulldate between getdate() and dateadd(day,15,Getdate())

I have a dim table for dates and have an isholiday = 0 or 1. I just can't figure out how to include this in the DATEADD expression.

All replies (7)

Tuesday, March 17, 2015 6:28 PM ✅Answered

Hi ,

  Try using the Rank function as below to get the next 15 business days. Consider the below as pseudo code.

SELECT *
  FROM 
(
SELECT RANK() OVER(ORDER BY DATEKEY) RNK, * 
 FROM DIMDATE
 WHERE DateColumnValue >= GETDATE()
   AND isHoliday = 0 
 ) X
 WHERE RNK <= 15

Best Regards Sorna

Tuesday, March 17, 2015 6:59 PM ✅Answered

You're probably going to want to use a calendar table for this. I wrote an article on creating one (as a table or function) including holiday calculations here: http://social.technet.microsoft.com/wiki/contents/articles/29260.tsql-calendar-functions-and-tables.aspx

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

Tuesday, March 17, 2015 6:12 PM

Hi,

try this link

http://www.sqlservercentral.com/Forums/Topic1247790-391-1.aspx

Hope will help you,

Questo post è fornito "così com'è". Non conferisce garanzie o diritti di alcun tipo. Ricorda di usare la funzione "segna come risposta" per i post che ti hanno aiutato a risolvere il problema e "deseleziona come risposta" quando le risposte segnate non sono effettivamente utili. Questo è particolarmente utile per altri utenti che leggono il thread, alla ricerca di soluzioni a problemi similari. ENG: This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. Please Vote This As Helpful if it helps to solve your issue. This can be beneficial to other community members reading the thread.

Tuesday, March 17, 2015 6:39 PM

Try it like this...

SELECT 
*
FROM 
    dbo.MyTable mt
    CROSS APPLY (
        SELECT 
        COUNT(*) AS WeekDays
        FROM dbo.Calendar c
        WHERE 
            c.dt BETWEEN mt.BegDate AND mt.EndDate
            AND c.isWeekday = 1
        ) d

Jason Long

Tuesday, March 17, 2015 7:56 PM

Thank you. This makes sense and works.

Tuesday, March 17, 2015 7:58 PM

This would work also but is more complicated. I think this is something we would use for a view or stored procedure. Thank you for the article.

Tuesday, March 17, 2015 11:07 PM

Use a calendar table and not computations. The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY, 
 julian_business_nbr INTEGER NOT NULL, 
 ...);

INSERT INTO Calendar 
VALUES ('2007-04-05', 42), 
 ('2007-04-06', 43), -- good Friday 
 ('2007-04-07', 43), 
 ('2007-04-08', 43), -- Easter Sunday 
 ('2007-04-09', 44), 
 ('2007-04-10', 45); --Tuesday

To compute the business days from Thursday of this week to next
 Tuesdays:

SELECT (C2.julian_business_nbr - C1.julian_business_nbr)
  FROM Calendar AS C1, Calendar AS C2
 WHERE C1.cal_date = '2007-04-05',
   AND C2.cal_date = '2007-04-10'; 

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL