SQL DateDiff without weekends and public holidays

Question

I am looking for solution how to select number of days between two dates without weekends and public holidays.

So far I have this:

SELECT evnt.event_id,
       evnt.date_from,
       evnt.date_to,
       DATEDIFF(DD, evnt.date_from, evnt.date_to) 
       - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
       - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
       + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
       --- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to
       FROM events AS evnt

everything works fine untill I uncomment section:

- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to

What I am trying to achieve is to get number of working days in date range. Problem is with last step, where I am trying to substract all public holiday days from this range.

Can anyone help with this last step? It seems, that I am doing something wrong, but I cant figure out what.

Thank you in advance

Possible duplicate of [How to count date difference excluding weekend and holidays in MySQL](http://stackoverflow.com/questions/10330836/how-to-count-date-difference-excluding-weekend-and-holidays-in-mysql) — Abhijit Annaldas, Jan 14 '16 at 16:35

score 7 · Accepted Answer · answered Jan 14 '16 at 16:45

Try This:

SELECT evnt.event_id,
   evnt.date_from,
   evnt.date_to,
   DATEDIFF(DD, evnt.date_from, evnt.date_to) 
   - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
   - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
   + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
   - (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
   FROM events AS evnt

the uncomment should be a Subquery

--- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to

like this:

- (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)

Good solution but if there are multiple public holidays on the same day you will be off — Luke, Feb 14 '20 at 09:18

score 1 · Answer 2 · answered Jan 14 '16 at 16:37

Kajiyama,

Try this:

SELECT evnt.event_id,
       evnt.date_from,
       evnt.date_to,
       DATEDIFF(DD, evnt.date_from, evnt.date_to) 
       - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
       - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
       + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
       -(SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
       FROM events AS evnt

Looks like you were missing the SELECT statement before the COUNT(*)

Hiten004 · Answer 3 · 2016-01-14T16:52:25.047

Here is the diffrent answer with WITH common_table_expression (CTE)

;with  t as
(
select COUNT(*) as cnt FROM public_holidays 
WHERE date_from BETWEEN evnt.date_from AND evnt.date_to
)
SELECT evnt.event_id,
       evnt.date_from,
       evnt.date_to,
       DATEDIFF(DD, evnt.date_from, evnt.date_to) 
       - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
       - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
       + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
       - (select cnt from T)
       FROM events AS evnt

score 1 · Answer 4 · answered Jan 14 '16 at 17:14

( I see this is already answered, but I'll throw this out anyways...)

Might not be a bad idea to have a calendar table with each day as a row, as opposed to a separate public_holidays table. Check out the article SQL Server Calendar Table for a demo and downloadable T-SQL code. Includes a couple of follow-on articles for querying and fiscal years.

score 0 · Answer 5 · answered Jan 29 '19 at 20:04

I saw some examples elsewhere like the below.

If you're looking for a 'business day diff' then please also compare it to a normal calendar day diff (i.e. DATEDIFF). Diff between two adjacent weekdays should be 1 day in both cases. Other solutions result in two business days between say Tuesday and Wednesday.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '1/17/19'
SET @EndDate = '1/18/19'

-- CalendarDateDiff vs Business Date Diff
SELECT
    DATEDIFF(d, @StartDate, @EndDate) AS CalendarDateDiff
    , (DATEDIFF(dd, @StartDate, @EndDate) + 1) 
    -1
    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
    -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS CalendarDays

Igor Krupitsky · Answer 6 · 2020-11-07T07:09:30.673

Three scalar functions: ShiftHolidayToWorkday, GetHoliday and GetWorkDays

create FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
    IF DATENAME( dw, @Date ) = 'Saturday'
        SET @Date = DATEADD(day, - 1, @Date)

    ELSE IF DATENAME( dw, @Date ) = 'Sunday'
        SET @Date = DATEADD(day, 1, @Date)

    RETURN @date
END
GO

create FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
    declare @s varchar(50)

    SELECT @s = CASE
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-01-01') = @date THEN 'New Year'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = @date THEN 'New Year'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-07-04') = @date THEN 'Independence Day'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-12-25') = @date THEN 'Christmas Day'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-12-31') = @date THEN 'New Years Eve'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-11-11') = @date THEN 'Veteran''s Day'

        WHEN [Month] = 1  AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' THEN 'Martin Luther King Day'
        WHEN [Month] = 5  AND [DayOfMonth] >= 25             AND [DayName] = 'Monday' THEN 'Memorial Day'
        WHEN [Month] = 9  AND [DayOfMonth] <= 7              AND [DayName] = 'Monday' THEN 'Labor Day'
        WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
        WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 23 AND 29 AND [DayName] = 'Friday' THEN 'Day After Thanksgiving'
        ELSE NULL END
    FROM (
        SELECT
            [Year] = YEAR(@date),
            [Month] = MONTH(@date),
            [DayOfMonth] = DAY(@date),
            [DayName]   = DATENAME(weekday,@date)
    ) c

    RETURN @s
END
GO

create FUNCTION [dbo].GetHolidays(@year int)
RETURNS TABLE 
AS
RETURN (  
    select dt, dbo.GetHoliday(dt) as Holiday
    from (
        select dateadd(day, number, convert(varchar,@year) + '-01-01') dt
        from master..spt_values 
        where type='p' 
        ) d
    where year(dt) = @year and dbo.GetHoliday(dt) is not null
)

create proc UpdateHolidaysTable
as

if not exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Holidays')
    create table Holidays(dt date primary key clustered, Holiday varchar(50))

declare @year int
set @year = 1990

while @year < year(GetDate()) + 20
begin
    insert into Holidays(dt, Holiday)
    select a.dt, a.Holiday
    from dbo.GetHolidays(@year) a
        left join Holidays b on b.dt = a.dt
    where b.dt is null

    set @year = @year + 1
end

create FUNCTION [dbo].[GetWorkDays](@StartDate DATE = NULL, @EndDate DATE = NULL)
RETURNS INT 
AS
BEGIN
    IF @StartDate IS NULL OR @EndDate IS NULL
        RETURN  0

    IF @StartDate >= @EndDate 
        RETURN  0

    DECLARE @Days int
    SET @Days = 0

    IF year(@StartDate) * 100 + datepart(week, @StartDate) = year(@EndDate) * 100 + datepart(week, @EndDate) 
        --same week
        select @Days = (DATEDIFF(dd, @StartDate, @EndDate))
      - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
      - (select count(*) from Holidays where dt between @StartDate and @EndDate)
    ELSE
        --diff weeks
        select @Days = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
      - (DATEDIFF(wk, @StartDate, @EndDate) * 2)
      - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
      - (select count(*) from Holidays where dt between @StartDate and @EndDate)
 
    RETURN  @Days
END

SQL DateDiff without weekends and public holidays

6 Answers6