我正在尝试计算从现在起 2 小时或更长时间后的日期，即使我将在周末或下班后开始计算，它应该是这样的： 工作时间为上午8点至下午4点 我从周五下午 3 点开始计算，所以如果我开始计算结果应该是周一上午 9 点
if(@data_przyj>@WorkStart AND DATEPART(DATEADD(MINUTE,@ileNaZapytanie,@data_przyj)<@WorkFinish)
BEGIN
while (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7))
BEGIN
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
SET @czyBylPrzeskok =1
END
if (@czyBylPrzeskok =1)
BEGIN
SET @LastDay = @CurrentDate
SET @LastDay = DATEADD(MINUTE, datediff(MINUTE,DATEADD(dd, 0, DATEDIFF(MINUTE, 0, @data_przyj)),@WorkStart), @LastDay)
SET @LastDay = DATEADD(HOUR, datediff(MINUTE,DATEADD(dd, 0, DATEDIFF(HOUR, 0, @data_przyj)),@WorkStart), @LastDay)
END
ELSE
BEGIN
SET @LastDay = DATEADD(MINUTE,@ileNaZapytanie,@data_przyj)
END
SET @IsCalculated = 1
END
else if(@data_przyj>@WorkStart AND DATEADD(MINUTE,@ileNaZapytanie,@data_przyj)>@WorkFinish)
BEGIN
SET @LastDay =DateADD(DD,3,GETDATE());
SET @IsCalculated = 1
END
else if(@data_przyj<@WorkStart )
BEGIN
SET @LastDay =GETDATE();
SET @IsCalculated = 1
END
END
编辑： 例如工作时间：8：00
- 16：00 我有日期"2019-09-06 15：00"，所以添加 2 个工作小时后应该是"2019-09-09 09：00"，日期"2019-09-06 13：00"应该是"2019-09-06 15：00"等以下解决方案使用包含工作时间的日历表，然后使用滚动总和累加每天的工作时间并查找需要结束的日期。
使用日历表将使您能够灵活地拥有不同的业务时间段，并且非常轻松地添加或删除假期。
设置(日历表(：
IF OBJECT_ID('tempdb..#WorkingCalendar') IS NOT NULL
DROP TABLE #WorkingCalendar
CREATE TABLE #WorkingCalendar (
Date DATE PRIMARY KEY,
IsWorkingDay BIT,
WorkingStartTime DATETIME,
WorkingEndTime DATETIME)
SET DATEFIRST 1 -- 1: Monday, 7: Sunday
DECLARE @StartDate DATE = '2019-01-01'
DECLARE @EndDate DATE = '2030-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 (
Date,
IsWorkingDay,
WorkingStartTime,
WorkingEndTime)
SELECT
Date = R.GeneratedDate,
IsWorkingDay = CASE
WHEN DATEPART(WEEKDAY, R.GeneratedDate) BETWEEN 1 AND 5 THEN 1 -- From Monday to Friday
ELSE 0 END,
WorkingStartTime = CASE
WHEN DATEPART(WEEKDAY, R.GeneratedDate) BETWEEN 1 AND 5
THEN CONVERT(DATETIME, R.GeneratedDate) + CONVERT(DATETIME, '08:00:00') END,
WorkingEndTime = CASE
WHEN DATEPART(WEEKDAY, R.GeneratedDate) BETWEEN 1 AND 5
THEN CONVERT(DATETIME, R.GeneratedDate) + CONVERT(DATETIME, '16:00:00') END
FROM
RecursiveDates AS R
OPTION
(MAXRECURSION 0)
生成如下所示的表：
+------------+--------------+-------------------------+-------------------------+
| Date | IsWorkingDay | WorkingStartTime | WorkingEndTime |
+------------+--------------+-------------------------+-------------------------+
| 2019-01-01 | 1 | 2019-01-01 08:00:00.000 | 2019-01-01 16:00:00.000 |
| 2019-01-02 | 1 | 2019-01-02 08:00:00.000 | 2019-01-02 16:00:00.000 |
| 2019-01-03 | 1 | 2019-01-03 08:00:00.000 | 2019-01-03 16:00:00.000 |
| 2019-01-04 | 1 | 2019-01-04 08:00:00.000 | 2019-01-04 16:00:00.000 |
| 2019-01-05 | 0 | NULL | NULL |
| 2019-01-06 | 0 | NULL | NULL |
| 2019-01-07 | 1 | 2019-01-07 08:00:00.000 | 2019-01-07 16:00:00.000 |
| 2019-01-08 | 1 | 2019-01-08 08:00:00.000 | 2019-01-08 16:00:00.000 |
| 2019-01-09 | 1 | 2019-01-09 08:00:00.000 | 2019-01-09 16:00:00.000 |
| 2019-01-10 | 1 | 2019-01-10 08:00:00.000 | 2019-01-10 16:00:00.000 |
| 2019-01-11 | 1 | 2019-01-11 08:00:00.000 | 2019-01-11 16:00:00.000 |
| 2019-01-12 | 0 | NULL | NULL |
| 2019-01-13 | 0 | NULL | NULL |
| 2019-01-14 | 1 | 2019-01-14 08:00:00.000 | 2019-01-14 16:00:00.000 |
| 2019-01-15 | 1 | 2019-01-15 08:00:00.000 | 2019-01-15 16:00:00.000 |
| 2019-01-16 | 1 | 2019-01-16 08:00:00.000 | 2019-01-16 16:00:00.000 |
| 2019-01-17 | 1 | 2019-01-17 08:00:00.000 | 2019-01-17 16:00:00.000 |
+------------+--------------+-------------------------+-------------------------+
建议的解决方案：
DECLARE @v_BusinessHoursToAdd INT = 2
DECLARE @v_CurrentDateTimeHour DATETIME = '2019-09-06 15:00'
;WITH CalendarFromNow AS
(
SELECT
T.Date,
WorkingStartTime = CASE
WHEN @v_CurrentDateTimeHour BETWEEN T.WorkingStartTime AND T.WorkingEndTime THEN @v_CurrentDateTimeHour
ELSE T.WorkingStartTime END,
WorkingEndTime = T.WorkingEndTime
FROM
#WorkingCalendar AS T
WHERE
T.Date >= CONVERT(DATE, @v_CurrentDateTimeHour) AND
T.IsWorkingDay = 1
),
RollingBusinessSum AS
(
SELECT
C.Date,
C.WorkingStartTime,
C.WorkingEndTime,
AmountBusinessHours = DATEDIFF(HOUR, C.WorkingStartTime, C.WorkingEndTime),
RollingBusinessHoursSum = SUM(DATEDIFF(HOUR, C.WorkingStartTime, C.WorkingEndTime)) OVER (ORDER BY C.Date),
PendingHours = @v_BusinessHoursToAdd - SUM(DATEDIFF(HOUR, C.WorkingStartTime, C.WorkingEndTime)) OVER (ORDER BY C.Date)
FROM
CalendarFromNow AS C
)
SELECT TOP 1
EndingHour = DATEADD(
HOUR,
R.PendingHours,
R.WorkingEndTime)
FROM
RollingBusinessSum AS R
WHERE
R.PendingHours < 0
ORDER BY
R.Date
解释：
第一个CTE CalendarFromNow只是从当前小时的日期过滤日历日期，并将开始工作日期时间减少到当前小时，因为这将是计算小时数的起点。
+------------+-------------------------+-------------------------+
| Date | WorkingStartTime | WorkingEndTime |
+------------+-------------------------+-------------------------+
| 2019-09-06 | 2019-09-06 15:00:00.000 | 2019-09-06 16:00:00.000 |
| 2019-09-09 | 2019-09-09 08:00:00.000 | 2019-09-09 16:00:00.000 |
| 2019-09-10 | 2019-09-10 08:00:00.000 | 2019-09-10 16:00:00.000 |
| 2019-09-11 | 2019-09-11 08:00:00.000 | 2019-09-11 16:00:00.000 |
| 2019-09-12 | 2019-09-12 08:00:00.000 | 2019-09-12 16:00:00.000 |
| 2019-09-13 | 2019-09-13 08:00:00.000 | 2019-09-13 16:00:00.000 |
| 2019-09-16 | 2019-09-16 08:00:00.000 | 2019-09-16 16:00:00.000 |
+------------+-------------------------+-------------------------+
第二个 CTERollingBusinessSum正在计算每天的工作时间，并在几天内累积它们。最后一列">待处理小时数"是我们需要从现在开始添加的小时数减去天数的工作时间总和的结果。
+------------+-------------------------+-------------------------+---------------------+-------------------------+--------------+
| Date | WorkingStartTime | WorkingEndTime | AmountBusinessHours | RollingBusinessHoursSum | PendingHours |
+------------+-------------------------+-------------------------+---------------------+-------------------------+--------------+
| 2019-09-06 | 2019-09-06 15:00:00.000 | 2019-09-06 16:00:00.000 | 1 | 1 | 1 |
| 2019-09-09 | 2019-09-09 08:00:00.000 | 2019-09-09 16:00:00.000 | 8 | 9 | -7 |
| 2019-09-10 | 2019-09-10 08:00:00.000 | 2019-09-10 16:00:00.000 | 8 | 17 | -15 |
| 2019-09-11 | 2019-09-11 08:00:00.000 | 2019-09-11 16:00:00.000 | 8 | 25 | -23 |
| 2019-09-12 | 2019-09-12 08:00:00.000 | 2019-09-12 16:00:00.000 | 8 | 33 | -31 |
| 2019-09-13 | 2019-09-13 08:00:00.000 | 2019-09-13 16:00:00.000 | 8 | 41 | -39 |
+------------+-------------------------+-------------------------+---------------------+-------------------------+--------------+
最后，"待处理小时数"列为负数的第一天是我们到达要添加的小时数的日期。这是ORDER BYTOP 1.为了获得最终日期时间，我们只需将待定小时数减去该特定日期的结束时间。
+------------+-------------------------+-------------------------+---------------------+-------------------------+--------------+-------------------------+
| Date | WorkingStartTime | WorkingEndTime | AmountBusinessHours | RollingBusinessHoursSum | PendingHours | EndingHour |
+------------+-------------------------+-------------------------+---------------------+-------------------------+--------------+-------------------------+
| 2019-09-09 | 2019-09-09 08:00:00.000 | 2019-09-09 16:00:00.000 | 8 | 9 | -7 | 2019-09-09 09:00:00.000 |
+------------+-------------------------+-------------------------+---------------------+-------------------------+--------------+-------------------------+
您可能需要调整性能并进行边界测试，但这可能会让您灵活地了解如何在节假日或不同时间段使用工作时间。