SQL Server counting hours between dates excluding

Question

I am looking for a SQL server function which can count the number of hours between 2 given datetime values, but excludes the hours between 6pm on Friday and 6am on Monday.

I'd like to be able to use this as a custom datediff, but also as a custom dateadd (eg adding 4 hours to 5pm a Friday, will return following Monday 9am)

I currently have something which excludes Sat/Sun based on the weekday number but this doesn't take the Fri/Mon hours into account.

Answer 1

This is another option you can use, with a calendar table.

This is the generation of the calendar table. For this case it has just days from monday to friday and from 9am to 6pm, one day per row (this can be additional columns for your regular calendar table).

IF OBJECT_ID('tempdb..#WorkingCalendar') IS NOT NULL
    DROP TABLE #WorkingCalendar

CREATE TABLE #WorkingCalendar (
    StartDateTime DATETIME,
    EndDateTime DATETIME)

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

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

;WITH RecursiveDates AS
(
    SELECT
        GeneratedDate = @StartDate

    UNION ALL

    SELECT
        GeneratedDate = DATEADD(DAY, 1, R.GeneratedDate)
    FROM
        RecursiveDates AS R
    WHERE
        R.GeneratedDate < @EndDate
)
INSERT INTO #WorkingCalendar (
    StartDateTime,
    EndDateTime)
SELECT
    StartDateTime = CONVERT(DATETIME, R.GeneratedDate) + CONVERT(DATETIME, '09:00:00'),
    EndDateTime = CONVERT(DATETIME, R.GeneratedDate) + CONVERT(DATETIME, '18:00:00')
FROM
    RecursiveDates AS R
WHERE
    DATEPART(WEEKDAY, R.GeneratedDate) BETWEEN 1 AND 5 -- From Monday to Friday
OPTION
    (MAXRECURSION 0)

---

This is the query to calculate time differences between 2 datetimes. You can change the HOUR for anything you want, in all 3 places (MINUTE, SECOND, etc.) and the result will be displayed in that unit.

DECLARE @FromDate DATETIME = '2018-06-15 18:00:00'
DECLARE @ToDate DATETIME = '2018-06-18 10:00:00'

;WITH TimeDifferences AS
(
    -- Date completely covered
    SELECT
        Difference = DATEDIFF(
            HOUR, 
            W.StartDateTime, 
            W.EndDateTime)
    FROM 
        #WorkingCalendar AS W
    WHERE 
        W.StartDateTime >= @FromDate AND 
        W.EndDateTime <= @ToDate

    UNION ALL

    -- Filter start date partially covered
    SELECT 
        Difference = DATEDIFF(
            HOUR, 
            @FromDate, 
            CASE WHEN W.EndDateTime > @ToDate THEN @ToDate ELSE W.EndDateTime END)
    FROM 
        #WorkingCalendar AS W
    WHERE 
        @FromDate BETWEEN W.StartDateTime AND W.EndDateTime

    UNION ALL

    -- Filter end date partially covered
    SELECT
        Difference = DATEDIFF(
            HOUR, 
            CASE WHEN W.StartDateTime > @FromDate THEN W.StartDateTime ELSE @FromDate END, 
            @ToDate)
    FROM 
        #WorkingCalendar AS W
    WHERE 
        @ToDate BETWEEN W.StartDateTime AND W.EndDateTime
)
SELECT 
    Total = SUM(T.Difference)
FROM 
    TimeDifferences AS T

This approach will consider each day from the calendar table, so if a particular day you have reduced hours (or maybe none from a Holiday) then the result will consider it.

---

You can use this query to add hours. Basically split each calendar range by hour, then use a row number to determine the amount of hours to add. Is this case you can't simply change the HOUR for MINUTE, it will require a few tweaks here and there if you need it.

DECLARE @FromDate DATETIME = '2018-06-14 12:23:12.661'
DECLARE @HoursToAdd INT = 15

;WITH RecursiveHourSplit AS
(
    SELECT
        StartDateTime = W.StartDateTime,
        EndDateTime = W.EndDateTime,
        HourSplitDateTime = W.StartDateTime
    FROM
        #WorkingCalendar AS W

    UNION ALL

    SELECT
        StartDateTime = W.StartDateTime,
        EndDateTime = W.EndDateTime,
        HourSplitDateTime = DATEADD(HOUR, 1, W.HourSplitDateTime)
    FROM
        RecursiveHourSplit AS W
    WHERE
        DATEADD(HOUR, 1, W.HourSplitDateTime) < W.EndDateTime
),
HourRowNumber AS
(
    SELECT
        R.HourSplitDateTime,
        RowNumber = ROW_NUMBER() OVER (ORDER BY R.HourSplitDateTime ASC)
    FROM
        RecursiveHourSplit AS R
    WHERE
        @FromDate < R.HourSplitDateTime
)
SELECT
    DATETIMEFROMPARTS(
        YEAR(R.HourSplitDateTime),
        MONTH(R.HourSplitDateTime),
        DAY(R.HourSplitDateTime),
        DATEPART(HOUR, R.HourSplitDateTime),

        DATEPART(MINUTE, @FromDate),
        DATEPART(SECOND, @FromDate),
        DATEPART(MILLISECOND, @FromDate))
FROM
    HourRowNumber AS R
WHERE
    R.RowNumber = @HoursToAdd

You can use similar logic to substract amount of hours by creating the row number with the rows that have datetimes before the supplied datetime (instead of after).

Answer 2

This uses a number table. Adjust weekend start/end in parmeters:

declare @d1 as datetime = '2018-06-01 05:30:00'
    , @d2 as datetime = '2018-06-18 19:45:00'
    , @FridayWE as int = 18 --6pm
    , @MondayWS as int = 6  --6am

;WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
select count(*) as HoursBetweenDatetimes
from (
    SELECT dateadd(hour, ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n, dateadd(hour, datediff(hour, 0, @d1), 0)) as [DateHour]
    FROM x ones,     x tens,      x hundreds,       x thousands
    ) a
where not ((DATEPART(dw,[DateHour]) = 6 and DATEPART(hour,[DateHour]) >= @FridayWE)
or (DATEPART(dw,[DateHour]) = 7 )
or (DATEPART(dw,[DateHour]) = 1 )
or (DATEPART(dw,[DateHour]) = 2 and DATEPART(hour,[DateHour]) < @MondayWS))
and [DateHour] < @d2