SQL 计算两个日期之间的营业时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5274208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate business hours between two dates
提问by Baran
How can I calculate business hours between two dates? For example we have two dates; 01/01/2010 15:00 and 04/01/2010 12:00 And we have working hours 09:00 to 17:00 in weekdays How can I calculate working hours with sql?
如何计算两个日期之间的营业时间？例如我们有两个日期；01/01/2010 15:00 和 04/01/2010 12:00 我们工作日的工作时间是 09:00 到 17:00 如何用 sql 计算工作时间？
回答by Kodak
Baran's answer fixed and modified for SQL 2005
Baran 的答案已针对 SQL 2005 修复和修改
SQL 2008 and above:
SQL 2008 及更高版本：
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 aras?ndaki i? saatlerini hafta sonlar?n? almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)
DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)
DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)
DECLARE @WorkStart TIME
SET @WorkStart = '09:00'
DECLARE @WorkFinish TIME
SET @WorkFinish = '17:00'
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
SQL 2005 and below:
SQL 2005 及以下：
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 aras?ndaki i? saatlerini hafta sonlar?n? almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATETIME
SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))
DECLARE @LastDay DATETIME
SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))
DECLARE @StartTime DATETIME
SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
DECLARE @FinishTime DATETIME
SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)
DECLARE @WorkStart DATETIME
SET @WorkStart = CONVERT(DATETIME, '09:00', 8)
DECLARE @WorkFinish DATETIME
SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATETIME
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATETIME
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
回答by navigator
I know this is post is very old but here is a function I wrote recently to calculate Business Hours/Minutes between any two events. It also takes into account any holidays which must be defined in a table.
我知道这篇文章很旧，但这是我最近编写的一个函数，用于计算任意两个事件之间的营业时间/分钟。它还考虑了必须在表中定义的任何假期。
The function returns the interval in minutes - you can divide by 60 to get hours as required.
该函数以分钟为单位返回间隔 - 您可以根据需要除以 60 以获得小时数。
This has been tested on SQL Server 2008. Hope it helps someone.
这已经在 SQL Server 2008 上进行了测试。希望它对某人有所帮助。
Create Function GetWorkingMin(@StartDate DateTime, @EndDate DateTime, @Country Varchar(2)) Returns Int
AS
Begin
Declare @WorkMin int = 0 -- Initialize counter
Declare @Reverse bit -- Flag to hold if direction is reverse
Declare @StartHour int = 9 -- Start of business hours (can be supplied as an argument if needed)
Declare @EndHour int = 17 -- End of business hours (can be supplied as an argument if needed)
Declare @Holidays Table (HDate DateTime) -- Table variable to hold holidayes
-- If dates are in reverse order, switch them and set flag
If @StartDate>@EndDate
Begin
Declare @TempDate DateTime=@StartDate
Set @StartDate=@EndDate
Set @EndDate=@TempDate
Set @Reverse=1
End
Else Set @Reverse = 0
-- Get country holidays from table based on the country code (Feel free to remove this or modify as per your DB schema)
Insert Into @Holidays (HDate) Select HDate from HOLIDAY Where COUNTRYCODE=@Country and HDATE>=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)
If DatePart(HH, @StartDate)<@StartHour Set @StartDate = DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)) -- If Start time is less than start hour, set it to start hour
If DatePart(HH, @StartDate)>=@EndHour+1 Set @StartDate = DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate)) -- If Start time is after end hour, set it to start hour of next day
If DatePart(HH, @EndDate)>=@EndHour+1 Set @EndDate = DateAdd(hour, @EndHour, DateDiff(DAY, 0, @EndDate)) -- If End time is after end hour, set it to end hour
If DatePart(HH, @EndDate)<@StartHour Set @EndDate = DateAdd(hour, @EndHour-24, DateDiff(DAY, 0, @EndDate)) -- If End time is before start hour, set it to end hour of previous day
If @StartDate>@EndDate Return 0
-- If Start and End is on same day
If DateDiff(Day,@StartDate,@EndDate) <= 0
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If day is between sunday and saturday
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- If day is not a holiday
If @EndDate<@StartDate Return 0 Else Set @WorkMin=DATEDIFF(MI, @StartDate, @EndDate) -- Calculate difference
Else Return 0
Else Return 0
End
Else Begin
Declare @Partial int=1 -- Set partial day flag
While DateDiff(Day,@StartDate,@EndDate) > 0 -- While start and end days are different
Begin
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If this is a weekday
Begin
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- If this is not a holiday
Begin
If @Partial=1 -- If this is the first iteration, calculate partial time
Begin
Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, DateAdd(hour, @EndHour, DateDiff(DAY, 0, @StartDate)))
Set @StartDate=DateAdd(hour, @StartHour+24, DateDiff(DAY, 0, @StartDate))
Set @Partial=0
End
Else Begin -- If this is a full day, add full minutes
Set @WorkMin=@WorkMin + (@EndHour-@StartHour)*60
Set @StartDate = DATEADD(DD,1,@StartDate)
End
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
Else Set @StartDate = DATEADD(DD,1,@StartDate)
End
If Datepart(dw,@StartDate)>1 And DATEPART(dw,@StartDate)<7 -- If last day is a weekday
If (Select Count(*) From @Holidays Where HDATE=DateAdd(dd, DateDiff(dd,0,@StartDate), 0)) = 0 -- And it is not a holiday
If @Partial=0 Set @WorkMin=@WorkMin + DATEDIFF(MI, @StartDate, @EndDate) Else Set @WorkMin=@WorkMin + DATEDIFF(MI, DateAdd(hour, @StartHour, DateDiff(DAY, 0, @StartDate)), @EndDate)
End
If @Reverse=1 Set @WorkMin=-@WorkMin
Return @WorkMin
End
回答by Matt Gibson
An alternative solution from @Pavanred's, coming at things from a more data-based angle:
@Pavanred's 的另一种解决方案，从更基于数据的角度来解决：
Create a table with all the dates you want to consider in it. For each day, set a number of working hours, like so:
创建一个包含您要考虑的所有日期的表格。对于每一天，设置一定数量的工作时间，如下所示：
WorkingDate Hours Comment
=========== ===== ==================
1 Jan 2011 0 Saturday
2 Jan 2011 0 Sunday
3 Jan 2011 0 Public Holiday
4 Jan 2011 8 Normal working day
5 Jan 2011 8 Normal working day
-- and so on, for all the days you want to report on.
This will take a small amount of setting up -- you can pre-populate it for weeks versus weekends automatically, then adjust for public holidays, etc, as necessary.
这将需要进行少量设置——您可以自动预先填充数周而不是周末，然后根据需要调整公共假期等。
But, what you lose in the setting up, you gain in ease of querying:
但是，您在设置中丢失了什么，您可以轻松查询：
SELECT
SUM(Hours)
FROM
working_days
WHERE
WorkingDate BETWEEN @StartDate AND @EndDate
...and this can work out as an easier approach if you need to start adding more complicated rules for what defines a working day, or if your working hours vary depending on the day, etc.
...如果您需要开始为定义工作日的内容添加更复杂的规则，或者您的工作时间因日期而异等，这可以作为一种更简单的方法来解决。
It also makes the rules more easily "editable", as you don't need to change any actual code to change the definitions of a working day, add public holidays, etc.
它还使规则更容易“编辑”，因为您不需要更改任何实际代码来更改工作日的定义、添加公共假期等。
回答by Jurgen Muller
The first step would be to calculate working days, as shown in the script below:
第一步是计算工作日，如下面的脚本所示：
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2), @DateFrom DATETIME, @DateTo DATETIME;
SET @DateFrom = '2017-06-05 11:19:11.287';
SET @DateTo = '2017-06-07 09:53:14.750';
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
????-(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
?? -CASE
????????????????????????????????????WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
????????????????????????????????????THEN 1
????????????????????????????????????ELSE 0
????????????????????????????????END+CASE
????????????????????????????????????????WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
????????????????????????????????????????THEN 1
????????????????????????????????????????ELSE 0
????????????????????????????????????END;
The second step involves getting a difference in seconds between the two dates and converting that difference into hours by dividing by?3600.0?as shown in this following?script:
第二步涉及获取两个日期之间的秒数差，然后通过除以 3600.0 将该差值转换为小时，如下面的脚本所示：
SET @TotalTimeDiff =
(
????SELECT DATEDIFF(SECOND,
?????????????????? (
?????????????????????? SELECT CONVERT(TIME, @DateFrom)
?????????????????? ),
?????????????????? (
?????????????????????? SELECT CONVERT(TIME, @DateTo)
?????????????????? )) / 3600.0
);
The last part involves multiplying the output the first step above by 24 (total number of hours in a day) and then later adding that to the output of the second step:
最后一部分涉及将上述第一步的输出乘以 24（一天中的总小时数），然后将其添加到第二步的输出中：
SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff;
Finally, the complete script that can be used to create a user defined function for calculating working hours is shown below:
最后，可用于创建用于计算工时的用户定义函数的完整脚本如下所示：
CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours]
(
????@DateFrom Datetime,
????@DateTo Datetime
)
RETURNS DECIMAL(18,2)
AS
BEGIN
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2)
SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo)
????-(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2)
?? -CASE
????????????????????????????????????WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'
????????????????????????????????????THEN 1
????????????????????????????????????ELSE 0
????????????????????????????????END+CASE
????????????????????????????????????????WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'
????????????????????????????????????????THEN 1
????????????????????????????????????????ELSE 0
????????????????????????????????????END;
SET @TotalTimeDiff =
(
????SELECT DATEDIFF(SECOND,
?????????????????? (
?????????????????????? SELECT CONVERT(TIME, @DateFrom)
?????????????????? ),
?????????????????? (
?????????????????????? SELECT CONVERT(TIME, @DateTo)
?????????????????? )) / 3600.0
);
RETURN??(SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff)
END
GO
The complete method is descibed in this article: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/?????
完整的方法在这篇文章中有描述：https: //www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/?????
回答by pavanred
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @WORKINGHOURS INT
DECLARE @Days INT
SET @StartDate = '2010/01/01'
SET @EndDate = '2010/04/01'
--number of working days
SELECT @Days =
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
--8 hours a day
SET @WORKINGHOURS = @Days * 8
SELECT @WORKINGHOURS
回答by Baran
-- =============================================
-- Author: Baran Kaynak
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 aras?ndaki i? saatlerini hafta sonlar?n? almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)
DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)
DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)
DECLARE @WorkStart TIME
SET @WorkStart = '09:30'
DECLARE @WorkFinish TIME
SET @WorkFinish = '17:30'
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
DECLARE @CurrentDate DATE
SET @CurrentDate = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDate DATE
SET @LastDate = CONVERT(DATE, @FinishDate, 112)
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = (@Temp + (9*60))
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartDate, @FinishDate)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
GO
回答by gaurav
ALTER FUNCTION WorkTime_fn (@StartDate DATETIME, @FinishDate DATETIME)
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay VARCHAR(9)
SET @FirstDay = CONVERT(VARCHAR(9),@StartDate, 112)
DECLARE @LastDay VARCHAR(9)
SET @LastDay = CONVERT(VARCHAR(9),@FinishDate, 112)
DECLARE @StartTime VARCHAR(9)
SET @StartTime = CONVERT(VARCHAR(9),@StartDate, 108)
DECLARE @FinishTime VARCHAR(9)
SET @FinishTime = CONVERT(VARCHAR(9),@FinishDate, 108)
DECLARE @WorkStart VARCHAR(9)
SET @WorkStart = '09:30:00'
DECLARE @WorkFinish VARCHAR(9)
SET @WorkFinish = '17:30:00'
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
DECLARE @CurrentDate VARCHAR(9)
SET @CurrentDate = CONVERT(VARCHAR(9),@StartDate, 112)
DECLARE @LastDate VARCHAR(9)
SET @LastDate = CONVERT(VARCHAR(9),@FinishDate, 112)
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = (@Temp + (8*60))
END
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = CONVERT(VARCHAR(9),DATEADD(day, 1, @CurrentDate),112)
END
Return @TEMP
END
回答by Ben Gulley
Here is an inline version Start/EndDateTime like 2015-03-16 09:52:24.000 Start/EndTime (businesshours) like 07:00:00 It is bulky but works in your select statement
这是一个内联版本 Start/EndDateTime like 2015-03-16 09:52:24.000 Start/EndTime (businesshours) like 07:00:00 它体积庞大但适用于您的选择语句
I will post it in Function version as well.
我也会把它发布到 Function 版本中。
Case when <StartDate>= <EndDate> then 0
When Convert(date,<StartDate>) = Convert(date,<EndDate>) Then
IIF( DATEPART(Dw,<StartDate>) in(1,7)
or Convert(time,<StartDate>) > Convert(time,<EndTime>)
or Convert(time,<EndDate>) < Convert(time,<StartTime>),0,
DateDiff(S,IIF(Convert(time,<StartDate>) < Convert(time,<StartTime>),Convert(time,<StartTime>),Convert(time,<StartDate>))
,IIF(Convert(time,<EndDate>) > Convert(time,<EndTime>), Convert(time,<EndTime>), Convert(time,<EndDate>))))
when Convert(date,<StartDate>) <> Convert(date,<EndDate>) then
IIF(DATEPART(Dw,<StartDate>) in(1,7) or Convert(time,<StartDate>) > Convert(time,<EndTime>),0 ,DateDiff(S,IIF(Convert(time,<StartDate>) < Convert(time,<StartTime>),Convert(time,<StartTime>),Convert(time,<StartDate>)), Convert(time,<EndTime>)))
+ IIF(DATEPART(Dw,<EndDate>) in(1,7) or Convert(time,<EndDate>) < Convert(time,<StartTime>),0,DateDiff(S,Convert(time,<StartTime>),IIF(Convert(time,<EndDate>) > Convert(time,<EndTime>), Convert(time,<EndTime>), Convert(time,<EndDate>))))
else -333
end --as pday
+IIF(DatePart(wEEk,<StartDate>) = DatePart(wEEk,<EndDate>)
,0, (DateDiff(wk,dateadd(d,-datepart(dw,<StartDate>),dateadd(ww,1,<StartDate>)),DATEADD(wk, DATEDIFF(wk, 6, <EndDate>), 6)-1) * 5)) * Datediff(S, Convert(time,<StartTime>),Convert(time,<EndTime>)) --Fullweek_days
+Case When Convert(date,<StartDate>) = Convert(date,<EndDate>) then 0
When DatePart(wEEk,<StartDate>) <> DatePart(wEEk,<EndDate>) then
IIF( datepart(dw,<StartDate>) = 7,0,DateDIFF(DAY,<StartDate>+1,dateadd(d,-datepart(dw,<StartDate>),dateadd(ww,1,<StartDate>)))) -- beginFulldays
+IIF( datepart(dw,<EndDate>) = 1,0,DateDIFF(DAY,DATEADD(wk, DATEDIFF(wk, 6, <EndDate>), 6),<EndDate> -1)) --Endfulldays
When DatePart(wEEk,<StartDate>) = DatePart(wEEk,<EndDate>) then
DateDiff(DAY,<StartDate>+1,<EndDate> )
ELSE -333 END * Datediff(S, Convert(time,<StartTime>),Convert(time,<EndTime>))
Here is the Function Version:
这是功能版本：
CREATE FUNCTION [dbo].[rsf_BusinessTime]
(
@startDateTime Datetime,
@endDateTime Datetime ,
@StartTime VarChar(12),
@EndTime VarChar(12) )
RETURNS BIGINT
As
BEGIN
Declare @totalSeconds BigInt,
@SecondsInDay int,
@dayStart Time = Convert(time,@StartTime),
@dayEnd Time =Convert(time,@EndTime),
@SatAfterStart Datetime = dateadd(d,-datepart(dw,@startDateTime),dateadd(ww,1,@startDateTime)),
@Sunbeforend Datetime = DATEADD(wk, DATEDIFF(wk, 6, @endDateTime), 6)
-- This function calculates the seconds between the start and end dates provided for business hours.
-- It only returns the time between the @start and @end time (hour of day) of the work week.
-- Weekend days are removed.
-- Holidays are not considered.
Set @SecondsInDay = Datediff(S, @dayStart,@dayEnd)
Set @totalSeconds =
--first/last/sameday
Case when @startDateTime= @endDateTime then 0
When Convert(date,@startDateTime) = Convert(date,@endDateTime) Then
IIF( DATEPART(Dw,@startDateTime) in(1,7)
or Convert(time,@startDateTime) > @dayEnd
or Convert(time,@endDateTime) < @dayStart,0,
DateDiff(S,IIF(Convert(time,@startDateTime) < @dayStart,@dayStart,Convert(time,@startDateTime))
,IIF(Convert(time,@endDateTime) > @dayEnd, @dayEnd, Convert(time,@endDateTime))))
when Convert(date,@startDateTime) <> Convert(date,@endDateTime) then
IIF(DATEPART(Dw,@startDateTime) in(1,7) or Convert(time,@startDateTime) > @dayEnd,0 ,DateDiff(S,IIF(Convert(time,@startDateTime) < @dayStart,@dayStart,Convert(time,@startDateTime)), @dayEnd))
+ IIF(DATEPART(Dw,@endDateTime) in(1,7) or Convert(time,@endDateTime) < @dayStart,0,DateDiff(S,@dayStart,IIF(Convert(time,@endDateTime) > @dayEnd, @dayEnd, Convert(time,@endDateTime))))
else -333
end --as pday
+IIF(DatePart(wEEk,@startDateTime) = DatePart(wEEk,@endDateTime)
,0, (DateDiff(wk,@SatAfterStart,@Sunbeforend-1) * 5)) * @SecondsInDay --Fullweek_days
+Case When Convert(date,@startDateTime) = Convert(date,@endDateTime) then 0
When DatePart(wEEk,@startDateTime) <> DatePart(wEEk,@endDateTime) then
IIF( datepart(dw,@startDateTime) = 7,0,DateDIFF(DAY,@startDateTime+1,@SatAfterStart)) -- beginFulldays
+IIF( datepart(dw,@endDateTime) = 1,0,DateDIFF(DAY,@Sunbeforend,@endDateTime -1)) --Endfulldays
When DatePart(wEEk,@startDateTime) = DatePart(wEEk,@endDateTime) then
DateDiff(DAY,@startDateTime+1,@endDateTime )
ELSE -333 END * @SecondsInDay
Return @totalSeconds
END
回答by Vladimir Baranov
The question says that public holidays should not be considered, so this answer does just that - calculates business hours taking weekends into account, but ignoring possible public holidays.
问题说不应该考虑公共假期，所以这个答案就是这样 - 计算营业时间时考虑周末，但忽略可能的公共假期。
It also assumes that the given start and end date/times are during the business hours.
它还假设给定的开始和结束日期/时间在营业时间内。
With this assumption the code doesn't care about the time when the business day starts or ends, it cares only about the total number of business hours per day. In your example, there are 8 business hours between 09:00 and 17:00. It doesn't have to be a whole number. The formula below calculates it with one minute precision, but it is trivial to make it one second or any other precision.
有了这个假设，代码不关心工作日开始或结束的时间，它只关心每天的工作总小时数。在您的示例中，09:00 和 17:00 之间有 8 个营业时间。它不必是整数。下面的公式以一分钟的精度计算它，但使它成为一秒或任何其他精度是微不足道的。
If you need to take public holidays into account you'd need to have a separate table which would list dates for public holidays, which may differ from year to year and from state to state or country to country. The main formula may stay the same, but you'd need to subtract from its result hours for public holidays that fall within the given range of dates.
如果您需要考虑公共假期，您需要有一个单独的表格来列出公共假期的日期，这些日期可能因年、州或国家而异。主要公式可能保持不变，但您需要从给定日期范围内的公共假期的结果小时数中减去。
The formula
公式
SELECT
DATEDIFF(minute, StartDT, EndDT) / 60.0
- DATEDIFF(day, StartDT, EndDT) * 16
- DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM T
To understand how it works let's create a table with some sample data that covers various cases:
要了解它是如何工作的，让我们创建一个包含一些涵盖各种情况的示例数据的表：
DECLARE @T TABLE (StartDT datetime2(0), EndDT datetime2(0));
INSERT INTO @T VALUES
('2012-03-05 09:00:00', '2012-03-05 15:00:00'), -- simple part of the same day
('2012-03-05 10:00:00', '2012-03-06 10:00:00'), -- full day across the midnight
('2012-03-05 11:00:00', '2012-03-06 10:00:00'), -- less than a day across the midnight
('2012-03-05 10:00:00', '2012-03-06 15:00:00'), -- more than a day across the midnight
('2012-03-09 16:00:00', '2012-03-12 10:00:00'), -- over the weekend, less than 7 days
('2012-03-06 16:00:00', '2012-03-15 10:00:00'), -- over the weekend, more than 7 days
('2012-03-09 16:00:00', '2012-03-19 10:00:00'); -- over two weekends
The query
查询
SELECT
StartDT,
EndDT,
DATEDIFF(minute, StartDT, EndDT) / 60.0
- DATEDIFF(day, StartDT, EndDT) * 16
- DATEDIFF(week, StartDT, EndDT) * 16 AS BusinessHours
FROM @T;
produces the following result:
产生以下结果：
+---------------------+---------------------+---------------+
| StartDT | EndDT | BusinessHours |
+---------------------+---------------------+---------------+
| 2012-03-05 09:00:00 | 2012-03-05 15:00:00 | 6.000000 |
| 2012-03-05 10:00:00 | 2012-03-06 10:00:00 | 8.000000 |
| 2012-03-05 11:00:00 | 2012-03-06 10:00:00 | 7.000000 |
| 2012-03-05 10:00:00 | 2012-03-06 15:00:00 | 13.000000 |
| 2012-03-09 16:00:00 | 2012-03-12 10:00:00 | 2.000000 |
| 2012-03-06 16:00:00 | 2012-03-15 10:00:00 | 50.000000 |
| 2012-03-09 16:00:00 | 2012-03-19 10:00:00 | 42.000000 |
+---------------------+---------------------+---------------+
It works, because in SQL Server DATEDIFFreturns the count of the specified datepartboundaries crossed between the specified startdateand enddate.
它有效，因为在 SQL Server 中DATEDIFF返回指定的startdate和enddate之间跨越的指定日期部分边界的计数。
Each day has 8 business hours. I calculate total number of hours between two dates, then subtract the number of midnights multiplied by 16 non-business hours per day, then subtract the number of weekends multiplied by 16 (8+8 business hours for Sat+Sun).
每天有8个工作时间。我计算两个日期之间的总小时数，然后减去午夜数乘以每天 16 个非营业时间，然后减去周末数乘以 16（周六+周日为 8+8 个营业时间）。
回答by Nugsson
Here's an alternative solution, without the use of a function. Note that this relies on the existence of a numbers table, populated with at least the maximum number of days the tasks you're tracking may take.
这是一个替代解决方案，不使用函数。请注意，这依赖于数字表的存在，至少填充了您正在跟踪的任务可能需要的最大天数。
This doesn't take public holidays into account. If you don't work weekends, setting the opening and closing times to midnight in the @OpeningHours table variable should do the job.
这不考虑公共假期。如果您周末不工作，在@OpeningHours 表变量中将开放和关闭时间设置为午夜应该可以完成这项工作。
I've tested this against 8500 rows of 'real world' data and found it to be performant.
我已经对 8500 行“真实世界”数据进行了测试，发现它的性能很好。
DECLARE @OpeningHours TABLE ([DayOfWeek] INTEGER, OpeningTime TIME(0), ClosingTime TIME(0));
INSERT
@OpeningHours ([DayOfWeek], OpeningTime, ClosingTime)
VALUES
(1, '10:00', '16:00') -- Sun
, (2, '06:30', '23:00') -- Mon
, (3, '06:30', '23:00') -- Tue
, (4, '06:30', '23:00') -- Wed
, (5, '06:30', '23:00') -- Thu
, (6, '06:30', '23:00') -- Fri
, (7, '08:00', '20:00'); -- Sat
DECLARE @Tasks TABLE ([Description] VARCHAR(50), CreatedDateTime DATETIME, CompletedDateTime DATETIME);
INSERT
@Tasks ([Description], CreatedDateTime, CompletedDateTime)
VALUES
('Make tea', '20170404 10:00', '20170404 10:12')
, ('Make coffee', '20170404 23:35', '20170405 06:32')
, ('Write complex SQL query', '20170406 00:00', '20170406 23:32')
, ('Rewrite complex SQL query', '20170406 23:50', '20170410 10:50');
SELECT
WorkingMinutesToRespond =
SUM(CASE WHEN CAST(Tasks.CreatedDateTime AS DATE) = CAST(Tasks.CompletedDateTime AS DATE) THEN
CASE WHEN CAST(Tasks.CreatedDateTime AS TIME) < OpeningHours.OpeningTime THEN
-- Task created before opening time
DATEDIFF(MINUTE, OpeningHours.OpeningTime, CAST(Tasks.CompletedDateTime AS TIME))
ELSE
DATEDIFF(MINUTE, Tasks.CreatedDateTime, Tasks.CompletedDateTime)
END
ELSE
CASE WHEN Tasks.CoveredDate = CAST(Tasks.CreatedDateTime AS DATE) THEN
-- This is the day the task was created
CASE WHEN CAST(Tasks.CreatedDateTime AS TIME(0)) > OpeningHours.ClosingTime THEN
0 -- after working hours
ELSE
-- during or before working hours
CASE WHEN CAST(Tasks.CreatedDateTime AS TIME(0)) < OpeningHours.OpeningTime THEN
-- before opening time; take the whole day into account
DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
ELSE
-- during opening hours; take part of the day into account
DATEDIFF(MINUTE, CAST(Tasks.CreatedDateTime AS TIME), OpeningHours.ClosingTime)
END
END
ELSE
-- This is the day the task was completed
CASE WHEN Tasks.CoveredDate = CAST(Tasks.CompletedDateTime AS DATE) THEN
CASE WHEN CAST(Tasks.CompletedDateTime AS TIME(0)) < OpeningHours.OpeningTime THEN
0 -- before working hours (unlikely to occur)
ELSE
-- during or after working hours
CASE WHEN CAST(Tasks.CompletedDateTime AS TIME(0)) > OpeningHours.ClosingTime THEN
-- after closing time (also unlikely); take the whole day into account
DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
ELSE
-- during opening hours; take part of the day into account
DATEDIFF(MINUTE, OpeningHours.OpeningTime, CAST(Tasks.CompletedDateTime AS TIME(0)))
END
END
ELSE
DATEDIFF(MINUTE, OpeningHours.OpeningTime, OpeningHours.ClosingTime)
END
END
END)
, Tasks.Description
, Tasks.CreatedDateTime
, Tasks.CompletedDateTime
FROM
(
SELECT
Tasks.Description
, Tasks.CreatedDateTime
, Tasks.CompletedDateTime
, CoveredDate = CAST(DATEADD(DAY, Numbers.Number, Tasks.CreatedDateTime) AS DATE)
FROM
@Tasks Tasks
INNER JOIN (SELECT * FROM Numbers WHERE Number >= 0) Numbers ON DATEDIFF(DAY, Tasks.CreatedDateTime, Tasks.CompletedDateTime) >= Numbers.Number
) Tasks
INNER JOIN @OpeningHours OpeningHours ON DATEPART(WEEKDAY, Tasks.CoveredDate) = OpeningHours.[DayOfWeek]
GROUP BY
Tasks.Description
, Tasks.CreatedDateTime
, Tasks.CompletedDateTime
ORDER BY
Tasks.CompletedDateTime;
