Datetime difference excluding weekends in days, hh:mm:ss format

Question

I'm trying to get the difference between two datetime2 fields excluding weekends.

I was able to do it only for the date but struggling to include the time:

WITH cte AS (
  SELECT
    start_datetime, end_datetime,
    cast(datediff(dd, start_datetime, end_datetime) - 
      (datediff(wk, start_datetime, end_datetime) * 2) -
      case when datepart(dw, start_datetime) = 1 then 1  else 0 end +
      case when datepart(dw, end_datetime) = 1 then 1  else 0 end
      as varchar(10)) as datediff_,
    DATEDIFF(SECOND, right(start_datetime, 16), right(end_datetime, 16)) timediff_
  from mytable
)
SELECT *, datediff_ + ' days, ' +
  CONVERT(VARCHAR(5), timediff_/60/60)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), timediff_/60%60), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(2), timediff_ % 60), 2) durationwoweekends
FROM cte;

This does calculate and return good when timediff_ is positive, but when negative I get a bad output:

the output format of column durationwoweekends that I'm aiming at is days, hh:mm:ss now outputting a varchar which is prob wrong and also forcing string 'days, ' but ultimately would need the field to be duration data type.

testing data:

CREATE TABLE #temptable(
start_datetime datetime2,
end_datetime datetime2
)
Insert into #temptable(start_datetime, end_datetime) values ('2022-10-31 10:30:52', '2022-11-02 23:47:55')
Insert into #temptable(start_datetime, end_datetime) values ('2022-11-01 08:30:46', '2022-11-03 02:59:27')

select * from #temptable

I'm using SQL Server 2019. Any help from any one will be greatly appreciated.

Answer 1

It is unusual to ignore (or adjust for) weekend dates, but not adjust for times at the same time. That might lead to strange results for cases like:

1. "Saturday 1600" to "Sunday 08:00" being calculated as "0 days -8 hours".
2. "Saturday 0900" to "Monday 16:00" being calculated as a sorter interval than "Sunday 0800" to "Monday 16:00".

If ignoring weekend days, I believe you should ignore them in their entirety, including any time components. This can be done by mapping all datetimes from 00:00 Saturday through 24:00 Sunday as equivalent to 00:00 Monday. That would make the resulting calculations continuous and consistent. Effectively, the elapsed timer is paused from 00:00 Saturday through 24:00 Sunday (00:00 Monday).

As for calculating the date and time components for display, I would suggest:

1. Adjust the start and end datetimes, as described above, to map any weekend values to 00:00 Monday.
2. Calculate a single elapsed time value in seconds.
3. If negative, separate out the sign and continue with the absolute value.
4. Split the elapsed seconds into day and time components.
5. Adjust the day value to account for the skipped weekends.
6. Combine and format the results for display.

The following will accomplish this:

-- The weekday value from DATEPART(weekday) is locale/settings dependent
-- So we need to calculate the expected values
--   For @@DateFirst = 7, @SaturdayDW = 7 and @SundayDW = 1
--   For @@DateFirst = 1, @SaturdayDW = 6 and @SundayDW = 7
--   For @@DateFirst = 6, @SaturdayDW = 1 and  @SundayDW = 2
-- DATEDIFF(week, ...) is unaffected by the DateFirst setting.
DECLARE @SaturdayDW INT = DATEPART(weekday, '20000101') -- Reference Saturday
DECLARE @SundayDW INT = DATEPART(weekday, '20000102') -- Reference Sunday
--SELECT @@DateFirst AS DateFirst, @SaturdayDW AS SaturdayDW, @SundayDW SundayDW
  
SELECT
    T.TestSet, T.StartDateTime, T.EndDateTime,
    LEFT(DATENAME(dw, T.StartDateTime), 3) AS SDay,
    LEFT(DATENAME(dw, T.EndDateTime), 3) AS EDay,
    FMT.AdjustedStart,
    FMT.AdjustedEnd,
    FMT.Elapsed
FROM TestData T
CROSS APPLY (
    -- Adjust weekend dates forward to 00:00:00 of the following Monday
    SELECT
        CASE DATEPART(dw, T.StartDateTime)
            WHEN @SaturdayDW
                THEN CONVERT(DATETIME, DATEADD(day, 2, CONVERT(DATE, T.StartDateTime)))
            WHEN @SundayDW
                THEN CONVERT(DATETIME, DATEADD(day, 1, CONVERT(DATE, T.StartDateTime)))
            ELSE T.StartDateTime
            END AS AdjStart,
        CASE DATEPART(dw, T.EndDateTime)
            WHEN @SaturdayDW
                THEN CONVERT(DATETIME, DATEADD(day, 2, CONVERT(DATE, T.EndDateTime)))
            WHEN @SundayDW
                THEN CONVERT(DATETIME, DATEADD(day, 1, CONVERT(DATE, T.EndDateTime)))
            ELSE T.EndDateTime
            END AS AdjEnd
) ADJ
CROSS APPLY (
    -- Calculate raw differences
    SELECT
        DATEDIFF(second, ADJ.AdjStart, ADJ.AdjEnd) AS Seconds,
        DATEDIFF(week, ADJ.AdjStart, ADJ.AdjEnd) AS Weeks
) DIFF1
CROSS APPLY (
    -- Calculate and adjust days, extract time-only component, handle negatives
    SELECT
        CASE WHEN DIFF1.Seconds < 0 THEN '-' ELSE '' END AS Sign,
        ABS(DIFF1.Seconds) / 86400 - 2 * ABS(DIFF1.Weeks) AS Days,
        DATEADD(second, ABS(DIFF1.Seconds) % 86400, 0) AS Time
) DIFF2
CROSS APPLY (
    -- Format intermediate data and results
    SELECT
       CONCAT(
           DIFF2.Sign,
           DIFF2.Days,
           CASE WHEN DIFF2.Days = 1 THEN ' Day, ' ELSE  ' Days, ' END,
           CONVERT(CHAR(8), DIFF2.Time, 108)
           ) AS Elapsed,
       CONCAT(
           LEFT(DATENAME(dw, ADJ.AdjStart), 3),
           ', ',
           CONVERT(CHAR(8), ADJ.AdjStart, 108)
           ) AS AdjustedStart,
       CONCAT(
           LEFT(DATENAME(dw, ADJ.AdjEnd), 3),
           ', ',
           CONVERT(CHAR(8), ADJ.AdjEnd, 108)
           ) AS AdjustedEnd
) FMT
ORDER BY T.TestSet, T.StartDateTime, T.EndDateTime

CROSS APPLYs are used above to conveniently encapsulate intermediate calculations, avoid duplication of subexpressions, and to reduce clutter in the final select list.

Care must be taken when working with DATEPART(weekday, ...) values, as the values returned are dependent on the DATEFIRST setting, which may default to either 1 or 7 depending on the locale. The logic above handles this by first calculating comparison weekday values for known Saturday and Sunday reference dates.

The DATEDIFF(second, ...) produces an integer result good for up to about 38 years. If longer intervals are needed, DATEDIFF_BIG() may be used.

Selected Results:

Test Set	Start Date/Time	End Date/Time	SDay	EDay	Adjusted Start	Adjusted End	Elapsed
1	2022-10-31 10:30:52	2022-10-31 10:30:52	Mon	Mon	Mon, 10:30:52	Mon, 10:30:52	0 Days, 00:00:00
1	2022-10-31 10:30:52	2022-11-02 23:47:55	Mon	Wed	Mon, 10:30:52	Wed, 23:47:55	2 Days, 13:17:03
1	2022-11-02 23:47:55	2022-10-31 10:30:52	Wed	Mon	Wed, 23:47:55	Mon, 10:30:52	-2 Days, 13:17:03
	...
2	2022-11-01 08:30:46	2022-11-03 02:59:27	Tue	Thu	Tue, 08:30:46	Thu, 02:59:27	1 Day, 18:28:41
2	2022-11-03 02:59:27	2022-11-01 08:30:46	Thu	Tue	Thu, 02:59:27	Tue, 08:30:46	-1 Day, 18:28:41
	...
3	2023-12-01 08:00:00	2023-12-01 16:45:00	Fri	Fri	Fri, 08:00:00	Fri, 16:45:00	0 Days, 08:45:00
3	2023-12-01 08:00:00	2023-12-02 08:00:00	Fri	Sat	Fri, 08:00:00	Mon, 00:00:00	0 Days, 16:00:00
3	2023-12-01 08:00:00	2023-12-03 16:45:00	Fri	Sun	Fri, 08:00:00	Mon, 00:00:00	0 Days, 16:00:00
3	2023-12-01 08:00:00	2023-12-04 16:45:00	Fri	Mon	Fri, 08:00:00	Mon, 16:45:00	1 Day, 08:45:00
	...
3	2023-12-02 08:00:00	2023-12-01 16:45:00	Sat	Fri	Mon, 00:00:00	Fri, 16:45:00	-0 Days, 07:15:00
3	2023-12-02 08:00:00	2023-12-03 16:45:00	Sat	Sun	Mon, 00:00:00	Mon, 00:00:00	0 Days, 00:00:00
3	2023-12-02 08:00:00	2023-12-04 08:00:00	Sat	Mon	Mon, 00:00:00	Mon, 08:00:00	0 Days, 08:00:00
3	2023-12-02 08:00:00	2023-12-04 16:45:00	Sat	Mon	Mon, 00:00:00	Mon, 16:45:00	0 Days, 16:45:00
3	2023-12-02 08:00:00	2023-12-31 23:59:59	Sat	Sun	Mon, 00:00:00	Mon, 00:00:00	20 Days, 00:00:00
	...
4	2023-12-01 10:00:05	2023-12-11 14:30:25	Fri	Mon	Fri, 10:00:05	Mon, 14:30:25	6 Days, 04:30:20

See this db<>fiddle for a demo with a variety of test data.