1. 首页
  2. 问题
  3. SQL DateDiff高级用法？
小编典典

SQL DateDiff高级用法？

sql

我需要计算两个日期之间的DateDiff（小时），但仅在工作时间（8:30-16:00，无周末）期间计算。然后，根据以下示例，将该结果放入“
Reaction_Time”列中。

ID日期Reaction_Time逾期
1 29.04.2003 15:00:00                      
1 30.04.2003 11:00:00 3:30        
2 30.04.2003 14:00:00                      
2 01.05.2003 14:00:00 7:30

*注意：我没有检查示例中的日期是否为假期。

我正在使用SQL Server 2005

这将与更大的查询结合在一起，但是现在我需要的只是这个入门，我将尝试弄清楚如何将它们自己组合在一起。谢谢您的帮助！

编辑：
嘿，谢谢大家的答复。但是由于SQL端解决方案的明显复杂性，决定我们将在Excel中执行此操作，因为无论如何该位置都将移动报告。抱歉给您带来麻烦，但我真的认为这比这要简单。就这样，我们只是没有时间。


阅读 183

收藏
2021-03-10

共1个答案

小编典典

DECLARE @BusHourStart DATETIME, @BusHourEnd DATETIME
SELECT @BusHourStart = ‘08:30:00’, @BusHourEnd = ‘16:00:00’
DECLARE @BusMinutesStart INT, @BusMinutesEnd INT
SELECT @BusMinutesStart = DATEPART(minute,@BusHourStart)+DATEPART(hour,@BusHourStart)60,
@BusMinutesEnd = DATEPART(minute,@BusHourEnd)+DATEPART(hour,@BusHourEnd)60
DECLARE @Dates2 TABLE (ID INT, DateStart DATETIME, DateEnd DATETIME)
INSERT INTO @Dates2
SELECT 1, ‘15:00:00 04/29/2003’, ‘11:00:00 04/30/2003’ UNION
SELECT 2, ‘14:00:00 04/30/2003’, ‘14:00:00 05/01/2003’ UNION
SELECT 3, ‘14:00:00 05/02/2003’, ‘14:00:00 05/06/2003’ UNION
SELECT 4, ‘14:00:00 05/02/2003’, ‘14:00:00 05/04/2003’ UNION
SELECT 5, ‘07:00:00 05/02/2003’, ‘14:00:00 05/02/2003’ UNION
SELECT 6, ‘14:00:00 05/02/2003’, ‘23:00:00 05/02/2003’ UNION
SELECT 7, ‘07:00:00 05/02/2003’, ‘08:00:00 05/02/2003’ UNION
SELECT 8, ‘22:00:00 05/02/2003’, ‘23:00:00 05/03/2003’ UNION
SELECT 9, ‘08:00:00 05/03/2003’, ‘23:00:00 05/04/2003’ UNION
SELECT 10, ‘07:00:00 05/02/2003’, ‘23:00:00 05/02/2003’ 

-- SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7

SELECT ID, DateStart, DateEnd, CONVERT(VARCHAR, Minutes/60) +':'+ CONVERT(VARCHAR, Minutes % 60) AS ReactionTime
FROM ( 
    SELECT ID, DateStart, DateEnd, Overtime,
        CASE 
            WHEN DayDiff = 0 THEN 
                CASE 
                    WHEN (MinutesEnd - MinutesStart - Overtime) > 0 THEN (MinutesEnd - MinutesStart - Overtime) 
                    ELSE 0 
                    END
            WHEN DayDiff > 0  THEN 
                CASE 
                    WHEN (StartPart + EndPart - Overtime) > 0 THEN (StartPart + EndPart - Overtime) 
                    ELSE 0 
                    END + DayPart
            ELSE 0
        END AS Minutes 
    FROM(
        SELECT ID, DateStart, DateEnd, DayDiff, MinutesStart, MinutesEnd,
                CASE WHEN(@BusMinutesStart - MinutesStart) > 0 THEN (@BusMinutesStart - MinutesStart) ELSE 0 END +
                CASE WHEN(MinutesEnd - @BusMinutesEnd) > 0 THEN (MinutesEnd - @BusMinutesEnd) ELSE 0 END AS Overtime, 
                CASE WHEN(@BusMinutesEnd - MinutesStart) > 0 THEN (@BusMinutesEnd - MinutesStart) ELSE 0 END AS StartPart,
                CASE WHEN(MinutesEnd - @BusMinutesStart) > 0 THEN (MinutesEnd - @BusMinutesStart) ELSE 0 END AS EndPart,
                CASE WHEN DayDiff > 1 THEN (@BusMinutesEnd - @BusMinutesStart)*(DayDiff - 1) ELSE 0 END AS DayPart
        FROM (
                SELECT DATEDIFF(d,DateStart, DateEnd) AS DayDiff, ID, DateStart, DateEnd,  
                DATEPART(minute,DateStart)+DATEPART(hour,DateStart)*60 AS MinutesStart,
                DATEPART(minute,DateEnd)+DATEPART(hour,DateEnd)*60 AS MinutesEnd 
                FROM (
                        SELECT ID,
                                CASE 
                                        WHEN DATEPART(dw, DateStart) = 7 
                                        THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 2))
                                        WHEN DATEPART(dw, DateStart) = 1 
                                        THEN DATEADD(SECOND, 1, DATEADD(DAY, DATEDIFF(DAY, 0, DateStart), 1))
                                ELSE DateStart END AS DateStart,
                                CASE 
                                        WHEN DATEPART(dw, DateEnd) = 7 
                                        THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), 0))
                                        WHEN DATEPART(dw, DateEnd) = 1 
                                        THEN DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, DateEnd), -1))
                                ELSE DateEnd END AS DateEnd FROM @Dates2
                )Weekends
        )InMinutes
    )Overtime
)Calculation
2021-03-10