SQL Server 计算日期之间的小时数，不包括周五下午 6 点至周一早上 6 点 [英] SQL Server counting hours between dates excluding Fri 6pm - Mon 6am

问题描述

我正在寻找一个 SQL 服务器函数，它可以计算 2 个给定日期时间值之间的小时数，但不包括周五下午 6 点到周一早上 6 点之间的小时数.

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.

我希望能够将其用作自定义 datediff，但也可以用作自定义 dateadd(例如，将 4 小时添加到周五下午 5 点，将在周一上午 9 点之后返回)

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.

推荐答案

这使用了一个数字表.在参数中调整周末开始/结束:

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

这篇关于SQL Server 计算日期之间的小时数，不包括周五下午 6 点至周一早上 6 点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！