SQL DateDiff高级用法？

Question

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

ID           Date           Reaction_Time   Overdue
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          YES

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

我正在使用 SQL Server 2005

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

编辑：嘿，谢谢大家的回复。 但由于 SQL 端解决方案的明显复杂性，我们决定在 Excel 中执行此操作，因为无论如何报表都会移动到 Excel 中。 抱歉给您带来麻烦，但我真的认为它会比这更简单。 事实上，我们只是没有时间。

Answer 1

我建议构建一个用户定义的函数，根据您的规则计算营业时间的日期差异。

SELECT
  Id,
  MIN(Date) DateStarted,
  MAX(Date) DateCompleted,
  dbo.udfDateDiffBusinessHours(MIN(Date), MAX(Date)) ReactionTime
FROM
  Incident
GROUP BY
  Id

我不确定您的 Overdue 值来自何处，因此我在示例中将其保留。

在函数中，您可以编写比在查询中更具表现力的 SQL，并且不会因业务规则而阻塞查询，从而使其难以维护。

而且函数可以很容易地被重用。 扩展它以包括对假期的支持（我在这里考虑一个 Holidays 表）不会太难。 可以进行进一步的细化，而无需更改难以阅读的嵌套 SELECT/CASE WHEN 结构，这将是替代方案。

如果今天有时间，我会考虑编写一个示例函数。

---

编辑：这里有一些花里胡哨的东西，透明地计算周末：

ALTER FUNCTION dbo.udfDateDiffBusinessHours (
  @date1 DATETIME,
  @date2 DATETIME
) RETURNS DATETIME AS
BEGIN
  DECLARE @sat INT
  DECLARE @sun INT
  DECLARE @workday_s INT
  DECLARE @workday_e INT
  DECLARE @basedate1 DATETIME
  DECLARE @basedate2 DATETIME
  DECLARE @calcdate1 DATETIME
  DECLARE @calcdate2 DATETIME
  DECLARE @cworkdays INT
  DECLARE @cweekends INT
  DECLARE @returnval INT

  SET @workday_s = 510 -- work day start:  8.5 hours
  SET @workday_e = 960 -- work day end:   16.0 hours

    -- calculate Saturday and Sunday dependent on SET DATEFIRST option
  SET @sat = CASE @@DATEFIRST WHEN 7 THEN 7 ELSE 7 - @@DATEFIRST END 
  SET @sun = CASE @@DATEFIRST WHEN 7 THEN 1 ELSE @sat + 1 END 

  SET @calcdate1 = @date1
  SET @calcdate2 = @date2

  -- @date1: assume next day if start was after end of workday
  SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
  SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) > @workday_e
                   THEN @basedate1 + 1
                   ELSE @calcdate1
                   END

  -- @date1: if Saturday or Sunday, make it next Monday
  SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
  SET @calcdate1 = CASE DATEPART(dw, @basedate1)
                   WHEN @sat THEN @basedate1 + 2
                   WHEN @sun THEN @basedate1 + 1
                   ELSE @calcdate1
                   END

  -- @date1: assume @workday_s as the minimum start time
  SET @basedate1 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate1))
  SET @calcdate1 = CASE WHEN DATEDIFF(mi, @basedate1, @calcdate1) < @workday_s 
                   THEN DATEADD(mi, @workday_s, @basedate1)
                   ELSE @calcdate1
                   END

  -- @date2: assume previous day if end was before start of workday
  SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
  SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) < @workday_s
                   THEN @basedate2 - 1
                   ELSE @calcdate2
                   END

  -- @date2: if Saturday or Sunday, make it previous Friday
  SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
  SET @calcdate2 = CASE DATEPART(dw, @calcdate2)
                   WHEN @sat THEN @basedate2 - 0.00001
                   WHEN @sun THEN @basedate2 - 1.00001
                   ELSE @date2
                   END

  -- @date2: assume @workday_e as the maximum end time
  SET @basedate2 = DATEADD(dd, 0, DATEDIFF(dd, 0, @calcdate2))
  SET @calcdate2 = CASE WHEN DATEDIFF(mi, @basedate2, @calcdate2) > @workday_e
                   THEN DATEADD(mi, @workday_e, @basedate2)
                   ELSE @calcdate2
                   END

  -- count full work days (subtract Saturdays and Sundays)
  SET @cworkdays = DATEDIFF(dd, @basedate1, @basedate2)
  SET @cweekends = @cworkdays / 7
  SET @cworkdays = @cworkdays - @cweekends * 2

  -- calculate effective duration in minutes
  SET @returnval = @cworkdays * (@workday_e - @workday_s)
                   + @workday_e - DATEDIFF(mi, @basedate1, @calcdate1) 
                   + DATEDIFF(mi, @basedate2, @calcdate2) - @workday_e

  -- return duration as an offset in minutes from date 0
  RETURN DATEADD(mi, @returnval, 0)
END

该函数返回一个 DATETIME 值，表示距日期 0 的偏移量（即 "1900-01-01 00:00 :00")。 例如，8:00 小时的时间跨度将是 "1900-01-01 08:00:00"，25 小时将是 "1900-01-02 01:00:00 “。 函数结果是两个日期之间营业时间的时间差。 没有对加班的特殊处理/支持。

SELECT dbo.udfDateDiffBusinessHours('2003-04-29 15:00:00', '2003-04-30 11:00:00')
--> 1900-01-01 03:30:00.000

SELECT dbo.udfDateDiffBusinessHours('2003-04-30 14:00:00', '2003-05-01 14:00:00')
--> 1900-01-01 07:30:00.000

当 @date1 为下班时间时，该函数假定下一个可用工作日（08:30 h）开始，当 @date1 时，该函数假定上一个可用工作日（16:00 h）结束。 code>@date2 是下班时间。

“下一个/上一个可用”的意思是：

• 如果@date1是'2009-02-06 07:00:00'（星期五），它将变成'2009 -02-06 08:30:00'（星期五）
• 如果 @date1 为 '2009-02-06 19:00:00'（星期五） 它将变为 '2009-02-09 08:30:00' (Mon)
• ，如果 @date2 为 '2009-02-09 07:00， 它将变成'2009-02-06 16:00:00'（星期五）
• ： 00'（星期一），如果@date2是'， 2009-02-09 19:00:00'（星期一），它将变成'2009-02-09 16:00:00'（星期一）

Answer 2

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

Answer 3

select datediff(hh,@date1,@date2) - 16.5*(datediff(dd,@date1,@date2))

唯一的问题是，它会给你 3:30 为 3.5 小时，但你可以轻松解决这个问题。

Answer 4

使用此代码：找出日期之间的周末

 (
    DATEDIFF(dd, open_date, zassignment_date) + 1 
    - ( (DATEDIFF(dd, open_date, zassignment_date) + 1) 
    -(DATEDIFF(wk, open_date, zassignment_date) * 2) 
    -(CASE WHEN DATENAME(dw,  open_date) = 'Sunday' THEN 1 ELSE 0 END) 
    -(CASE WHEN DATENAME(dw, zassignment_date) = 'Saturday' THEN 1 ELSE 0 END) )) wk_end 

Answer 5

假设您有工作日（及其时间）的参考表，那么我将使用 3 阶段方法（伪 sql）

（首先排除“一日全部”的简单示例，因为这简化了逻辑）

 -- days that are neither the start nor end (full days)
 SELECT @FullDayHours = SUM(day start to day end)
 FROM   reference-calendar
 WHERE  Start >= midnight-after-start and End <= midnight-before-end

 -- time after the [query start] to the end of the first working day
 SELECT @FirstDayHours = [query start] to day end
 FROM   reference-calandar
 WHERE  start day

 -- time from the start of the last working day to the [query end]
 SELECT @LastDayHours = day start to [query end]
 FROM   reference-calendar
 WHERE  end-day

 IF @FirstDayHours < 0 SET @FirstDayHours = 0 -- starts outside working time
 IF @LastDayHours < 0 SET @LastDayHours  = 0 -- ends outside working time

 PRINT @FirstDayHours  + @FullDayHours + @LastDayHours

显然如果没有更多的背景，很难正确地做到这一点......

Answer 6

此功能将为您提供两个给定时间之间的营业时间差。 这将根据日期部分参数返回分钟或小时的差异。

CREATE FUNCTION [dbo].[fnBusinessHoursDateDiff] (@StartTime SmallDatetime, @EndTime SmallDateTime, @DatePart varchar(2)) RETURNS DECIMAL (10,2)
AS 
BEGIN

DECLARE @Minutes        bigint
    ,   @FinalNumber    Decimal(10,2)

-- // Create Minute By minute table for CTE
-- ===========================================================
;WITH  cteInputHours (StartTime, EndTime, NextTime) AS (
    SELECT  @StartTime  
        ,   @EndTime    
        ,   dateadd(mi, 1, @StartTime)
 ),
 cteBusinessMinutes (TimeOfDay, [isBusHour], NextTime) AS(
    SELECT  StartTime [TimeOfDay]
        ,   case when datepart(dw, StartTime) between 2 and 6 and convert(time,StartTime) between '08:30' and '15:59' then 1 else 0 end [isBusHour]
        ,   dateadd(mi, 1, @StartTime)  [NextTime]
    FROM    cteInputHours
    UNION ALL
    SELECT  dateadd(mi, 1, (a.TimeOfDay)) [TimeOfDay]
        ,   case when datepart(dw, a.TimeOfDay) between 2 and 6 and  convert(time,dateadd(mi, 1, (a.TimeOfDay)) ) between '08:30' and '15:59' then 1 else 0 end [isBusHour]
        ,   dateadd(mi, 2, (a.TimeOfDay)) NextTime
    FROM    cteBusinessMinutes a
    WHERE   dateadd(mi, 1, (a.TimeOfDay)) < @EndTime
) 
SELECT  @Minutes = count(*)
FROM    cteBusinessMinutes
WHERE   isBusHour = 1
OPTION (MAXRECURSION 0);

-- // Final Select
-- ===========================================================
SELECT  @FinalNumber = @Minutes / (case when @DatePart = 'hh' then 60.00 else 1 end)

RETURN @FinalNumber 

END