sql server - T-SQL | Calculate the number of hours between dates but ignore the hours from Weekend and only count 8-18 hours

Question

I'm trying to calculate the difference between two dates excluding the weekends and only count the time from 8pm - 6am. I want to calculate that difference in Days, Hours and Minutes.

For that I have this:

DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME

SET @Start_Date = '2017-06-23 10:43:41.000'
SET @End_Date = '2017-06-27 11:58:52.000'

SELECT (DATEDIFF(dd, @Start_Date, @End_Date) + 1)
                -(DATEDIFF(wk, @Start_Date, @End_Date) * 2)
                -(CASE WHEN DATENAME(dw, @Start_Date) = 'Sunday' THEN 1 ELSE 0 END)
                -(CASE WHEN DATENAME(dw, @End_Date) = 'Saturday' THEN 1 ELSE 0 END) AS [Time to First Atualization- Days],
            datediff(hour, @Start_Date, @End_Date) - (datediff(wk, @Start_Date, @End_Date) * 48) -
                case when datepart(dw, @Start_Date)  = 1 then 1 else 0 end +
                case when datepart(dw, @End_Date)  = 1 then 1 else 0 end AS [Time to First Atualization- Hours],
                datediff(minute, @Start_Date, @End_Date) - (datediff(wk, @Start_Date, @End_Date) * 2880) -
                case when datepart(dw, @Start_Date)  = 1 then 1 else 0 end +
                case when datepart(dw, @End_Date)  = 1 then 1 else 0 end AS [Time to First Atualization- Minutes]

The number of days the query return the correct value but to calculate the number of hours and minutes it's wrong...

How can I solve this?

Thanks!

Answer 1

I worked out something from scratch, and it seems to cover all your needs, though you can update us back if something's missing.

Considering it's a fresh start and coming from a different angle, you might discover certain techniques or ideas out of it. Also, it does seem simpler to me but maybe that's because I'm reviewing my own work...

One last note, I'll be relying on a trick I read before, that applies MIN and MAX in a row-wise fashion, abstract example:

SELECT MAX([value]) AS [MAX], MIN([value]) AS [MIN]
FROM (
VALUES (CURRENT_TIMESTAMP), (@Start_Date), (@End_Date), (NULL), (0)
) AS [data]([value])

First off, thought of figuring the amount of time outside start & end days:

SELECT MinutesExcludingStartAndEndDays = MAX([value])
FROM (VALUES (0), ((DATEDIFF(DAY, @Start_Date, @End_Date) - 1) * 840)) AS [data]([value])

Second, figuring the time during starting day, against 8pm (or end time if both days match):

SELECT MinutesOnStartDay = DATEDIFF(MINUTE, @Start_Date, MIN([value]))
FROM (VALUES (@End_Date), (DATETIMEFROMPARTS(YEAR(@Start_Date), MONTH(@Start_Date), DAY(@Start_Date), 20, 0, 0, 0))) AS [data]([value])

Third is very similar to second, however note that if start and end days were the same, we should not count both second and third. I decided to handle that with a CASE statement within third:

SELECT MinutesOnEndDayIfNotStartDay = CASE DATEDIFF(DAY, @Start_Date, @End_Date) WHEN 0 THEN 0 ELSE DATEDIFF(MINUTE, MAX([value]), @End_Date) END
FROM (VALUES (@Start_Date), (DATETIMEFROMPARTS(YEAR(@End_Date), MONTH(@End_Date), DAY(@End_Date), 6, 0, 0, 0))) AS [data]([value])

Fourth, if either start or end dates land on a weekend, it should be pushed away from there:

DECLARE @Mod int

SET @Mod = CONVERT(int, @Start_Date) % 7
IF @Mod IN (5, 6)
    SET @Start_Date = DATEADD(DAY, CASE @Mod WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0/0 END, DATETIMEFROMPARTS(YEAR(@Start_Date), MONTH(@Start_Date), DAY(@Start_Date), 6, 0, 0, 0))

SET @Mod = CONVERT(int, @End_Date) % 7
IF @Mod IN (5, 6)
    SET @End_Date = DATEADD(DAY, CASE @Mod WHEN 5 THEN -1 WHEN 6 THEN -2 ELSE 0/0 END, DATETIMEFROMPARTS(YEAR(@End_Date), MONTH(@End_Date), DAY(@End_Date), 20, 0, 0, 0))

Lastly, the issue of having weekend days fully encompassed within your target period, for that have a look at this question, from the votes there I can only guess they worked it out already.