计算两个日期之间的营业时间

本文介绍了计算两个日期之间的营业时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何计算两个日期之间的营业时间？ 例如我们有两个日期; 01/01/2010 15:00和04/01/2010 12:00 我们平日工作时间为09:00至17:00 如何使用sql计算工作时间？ p>

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?

推荐答案

Baran的答案修正并修改为SQL 2005

Baran's answer fixed and modified for SQL 2005

SQL 2008及以上：

SQL 2008 and above:

-- ============================================= -- 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及以下版本：

SQL 2005 and below:

-- ============================================= -- 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