-1

I have a query where I need to add 2 days to the date variable based on a condition.

However, this date variable needs to skip weekends and holidays.

I have a table that stores holidays. How do I integrate this into my query?

So currently I check if the date is a weekend. If so then I add 1 or 2 days. However, if that date is a holiday I then need to add another day. And then check if that date is a holiday or a weekend!

Is there a script that I could use for this?

  • 3
    When asking for a query, it helps to have table definitions and sample data, as well as expected output. Please see help us help you – Tom V Jul 8 '16 at 7:23
2

This is really a T-SQL question and would be better off on StackOverflow.

That said, you can use the following to determine if the date falls on a weekend:

SELECT DATENAME(dw,GETDATE()) -- Friday
SELECT DATEPART(dw,GETDATE()) -- 6

And determining if the date falls on a holiday (by querying against your holiday table) should be trivial.

I recommend you have a stab yourself. If it works, great - if not, post what you have so far, plus your specific problem on SO.

  • 2
    DATEPART() may return a different number based on DATEFIRST and possibly localization/regional settings. One can't assume that Friday always equals 6. – alroc Jul 7 '16 at 16:52
1

Use a most handy Calendar table, which is a table that holds a row for each day with custom markers (like IsHoliday, IsFirstOfMonth, IsFirstMondayOfMonth, etc.), and any other column you might need (like BusinessHourStartTime, BusinessHourEndTime, etc.).

You can generate one recursively following this SELECT.

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

DECLARE @StartDate DATE = '2018-01-01'
DECLARE @EndDate DATE = '2025-01-01'

DECLARE @HolidaysTbl as TABLE (HolidayDate date)
INSERT INTO @HolidaysTbl(HolidayDate) VALUES('2018-12-13'), ('2018-12-17'), ('2018-12-19');

;WITH GeneratedDates AS
(
    SELECT
        GeneratedDate = @StartDate

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate)
    FROM
        GeneratedDates AS G
    WHERE
        G.GeneratedDate < @EndDate
),
SpecialDays AS
(
    SELECT
        Date = G.GeneratedDate,
        Year = YEAR(G.GeneratedDate),
        Month = MONTH(G.GeneratedDate),
        Day = DAY(G.GeneratedDate),
        IsWeekend = CASE WHEN DATEPART(WEEKDAY, G.GeneratedDate) IN (6, 7) THEN 1 ELSE 0 END,
        IsHoliday = CASE WHEN H.HolidayDate IS NOT NULL THEN 1 ELSE 0 END
    FROM
        GeneratedDates AS G
        LEFT JOIN @HolidaysTbl AS H ON G.GeneratedDate = H.HolidayDate
)
SELECT
    Date = S.Date,
    Year = S.Year,
    Month = S.Month,
    Day = S.Day,
    IsWeekend = S.IsWeekend,
    IsHoliday = S.IsHoliday,
    BusinessHoursStartTime = CASE WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 THEN CONVERT(TIME, '09:00') END,
    BusinessHoursEndTIme = CASE WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 THEN CONVERT(TIME, '18:00') END,
    WorkingDaysOrder = CASE 
        WHEN S.IsHoliday = 0 AND S.IsWeekend = 0 
        THEN ROW_NUMBER() OVER (
            ORDER BY 
            S.IsHoliday ASC,
            S.IsWeekend ASC,
            S.Date ASC) END
FROM
    SpecialDays AS S
OPTION
    (MAXRECURSION 0)

Which generates rows like this:

Date        Year    Month   Day     IsWeekend   IsHoliday   BusinessHoursStartTime  BusinessHoursEndTIme    WorkingDaysOrder
2018-01-01  2018    1       1       0           0           09:00:00.0000000        18:00:00.0000000        1
2018-01-02  2018    1       2       0           0           09:00:00.0000000        18:00:00.0000000        2
2018-01-03  2018    1       3       0           0           09:00:00.0000000        18:00:00.0000000        3
2018-01-04  2018    1       4       0           0           09:00:00.0000000        18:00:00.0000000        4
2018-01-05  2018    1       5       0           0           09:00:00.0000000        18:00:00.0000000        5
2018-01-06  2018    1       6       1           0           NULL                    NULL                    NULL
2018-01-07  2018    1       7       1           0           NULL                    NULL                    NULL
2018-01-08  2018    1       8       0           0           09:00:00.0000000        18:00:00.0000000        6
2018-01-09  2018    1       9       0           0           09:00:00.0000000        18:00:00.0000000        7
2018-01-10  2018    1       10      0           0           09:00:00.0000000        18:00:00.0000000        8
2018-01-11  2018    1       11      0           0           09:00:00.0000000        18:00:00.0000000        9
2018-01-12  2018    1       12      0           1           NULL                    NULL                    NULL
2018-01-13  2018    1       13      1           0           NULL                    NULL                    NULL
2018-01-14  2018    1       14      1           0           NULL                    NULL                    NULL
2018-01-15  2018    1       15      0           0           09:00:00.0000000        18:00:00.0000000        10
2018-01-16  2018    1       16      0           0           09:00:00.0000000        18:00:00.0000000        11
2018-01-17  2018    1       17      0           0           09:00:00.0000000        18:00:00.0000000        12
2018-01-18  2018    1       18      0           0           09:00:00.0000000        18:00:00.0000000        13
2018-01-19  2018    1       19      0           0           09:00:00.0000000        18:00:00.0000000        14
2018-01-20  2018    1       20      1           0           NULL                    NULL                    NULL
2018-01-21  2018    1       21      1           0           NULL                    NULL                    NULL
2018-01-22  2018    1       22      0           0           09:00:00.0000000        18:00:00.0000000        15
2018-01-23  2018    1       23      0           0           09:00:00.0000000        18:00:00.0000000        16

So when you need to add days and the result has to be a working day you can simply find your queried date in the table, retrieve it's WorkingDaysOrder, add 2 days (for example) and bring back it's associated Date.

DECLARE @DateToAddDays DATE = '2018-01-10' -- Wednesday
DECLARE @AmountOfDaysToAdd INT = 2

SELECT 
    D.Date 
FROM
    Calendar AS D
WHERE
    D.WorkingDaysOrder = @AmountOfDaysToAdd + (SELECT T.WorkingDaysOrder FROM Calendar AS T WHERE T.Date = @DateToAddDays)

-- Result: 2018-01-15 (Monday, day 12 is Holiday, 13 and 14 weekend)

Calendar tables will help you with most of your dates problems, like counting working hours between 2 dates or finding the 3rd Sunday on each month. It doesn't have many rows and makes date and time handling very easy to read and maintain.

0

Thanks! Yes I managed to use the below script with my holidays tbl. The @numDays variable is the total days to add onto the initial date (@addDate)

 DECLARE @HolidaysTbl as TABLE
 (
   HolidayDate date
 );

 INSERT INTO @HolidaysTbl(HolidayDate) VALUES('2012-12-13'), ('2012-12-17'), ('2012-12-19');

--select * from @HolidaysTbl

declare @addDate AS DATE = '2012-12-13', @numDays AS  INT = 5;

select CAST(@addDate  AS  DATETIME), DATENAME(DW,CAST(@addDate  AS  DATETIME)) as 'InitialDate'

WHILE @numDays>0
BEGIN
   SET @addDate=DATEADD(d,1,@addDate);

   IF DATENAME(DW,@addDate) = 'saturday' SET @addDate = DATEADD(d,1,@addDate);
   IF DATENAME(DW,@addDate) = 'sunday' SET  @addDate = DATEADD(d,1,@addDate);
   WHILE @addDate IN (SELECT HolidayDate FROM @HolidaysTbl WHERE HolidayDate = @addDate)
   BEGIN
        SELECT 'TEST', @addDate, DATENAME(DW,CAST(@addDate  AS  DATETIME))
        SET  @addDate = DATEADD(d,1,@addDate);
        SELECT 'TEST2', @addDate, DATENAME(DW,CAST(@addDate  AS  DATETIME))
   END

   SET @numDays=@numDays-1;
END

select CAST(@addDate  AS  DATETIME), DATENAME(DW,CAST(@addDate  AS  DATETIME))
0

You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query.

    Declare @startdatee as DateTime
    Declare @enddatee as DateTime
    Set @startdatee = GETDATE();
    set @enddatee = GETDATE()+1;
    select @startdatee,@enddatee,(datediff(day,@startdatee,@enddatee+1)-(2)*datediff(week,@startdatee,@enddatee))

And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection. i gave you example here. You can use your holiday's table,here i am just using temp table for example.

Declare @holidays Table (ID int,Name Varchar(50),[Date] date)
Insert into @holidays VALUES 
(1,'holiday1','2018-07-07'),
(2,'holiday2','2018-07-12')

Declare 
    @startdate datetime = '2018-07-02',
    @enddate datetime =  '2018-07-30',
    @holiday int = 0

    Set @holiday = (Select COUNT(*) FROM @holidays WHERE (Date BETWEEN @startdate AND @enddate) 
                    AND DATEPART(Weekday,Date) not in (1,7)
                    )

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(case datepart(dw, @StartDate) when 1 then 1 else 0 end) 
  -(case datepart(dw, @EndDate) when 7 then 1 else 0 end)
  -@holiday

this is much simple and easy query then other.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.