场景描述:公司员工每天9:00-17:00,每周1-5是有效工作时间,给你两个时间段,要计算出有效的工作时间
比如说2013/2/4 12:21 -- 2013/2/9 14:33
求这段时间内员工的有效工作时间
求这段时间内员工的有效工作时间
USE [SHLG_OA] GO /****** Object: UserDefinedFunction [dbo].[fn_GetEffectiveWorkingTime] Script Date: 02/26/2013 17:05:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /************************************************** *自定义函数名 : --[fn_GetEffectiveWorkingTime] *函数功能 : --获取有效工作时间(周一到周五,9:00-17:00) *输入参数 : *输出参数 : 分钟 *作者 : --刘仁和 *创建时间 : --2012.11.7 *更新时间 : **************************************************/ ALTER FUNCTION [dbo].[fn_GetEffectiveWorkingTime](@starttime DATETIME, @endtime DATETIME) returns INT AS BEGIN DECLARE @t_starttime DATETIME=NULL --调整开始时间为规格时间,日期置为周一,时间置为上午9:00或下午5点 DECLARE @t_endtime DATETIME=NULL --调整结束时间为规格时间,日期置为周一,时间置为上午9:00或下午5点 DECLARE @day_dvalue INT=0 --天数部分差值 DECLARE @minute_dvalue INT=0 --时间部分差值 DECLARE @retrun_dvalue INT=0 --返回总差值 IF( @starttime IS NULL OR @endtime IS NULL ) RETURN 0 ELSE BEGIN --Datepart(weekday, @starttime + @@DateFirst - 1)获取星期 --若开始时间在周末,日期置于周五,时间置于下午5点 IF(Datepart(weekday, @starttime + @@DateFirst - 1)=6 OR Datepart(weekday, @starttime + @@DateFirst - 1)=7) BEGIN SET @starttime=Dateadd(day, -( Datepart(weekday, @starttime + @@DateFirst - 1) - 5 ), @starttime) SET @starttime=Cast(CONVERT(NVARCHAR(10), @starttime, 120) + ' 17:00:00.00' AS DATETIME) END --若结束时间在周末,日期置于周五,时间置于下午5点 IF(Datepart(weekday, @endtime + @@DateFirst - 1)=6 OR Datepart(weekday, @endtime + @@DateFirst - 1)=7) BEGIN SET @endtime=Dateadd(day, -( Datepart(weekday, @endtime + @@DateFirst - 1) - 5 ), @endtime) SET @endtime=Cast(CONVERT(NVARCHAR(10), @endtime, 120) + ' 17:00:00.00' AS DATETIME) END --开始日期置于周一 --注意:@t_starttime和@starttime是两个变量 SET @t_starttime=Dateadd(day, -( Datepart(weekday, @starttime + @@DateFirst - 1) - 1 ), @starttime) --结束日期置于周一 SET @t_endtime=Dateadd(day, -( Datepart(weekday, @endtime + @@DateFirst - 1) - 1 ), @endtime) --Convert(NVARCHAR(10), @t_starttime, 120)获取日期部分 IF( Datepart(hh, @starttime) < 9 ) BEGIN --开始时间置于上午9点 SET @t_starttime=Cast(CONVERT(NVARCHAR(10), @t_starttime, 120) + ' 9:00:00.00' AS DATETIME) END ELSE IF( Datepart(hh, @t_starttime) > 16 ) BEGIN --开始时间置于下午5点 SET @t_starttime=Cast(CONVERT(NVARCHAR(10), @t_starttime, 120) + ' 17:00:00.00' AS DATETIME) END IF( Datepart(hh, @t_endtime) < 9 ) BEGIN --结束时间置于上午9点 SET @t_endtime=Cast(CONVERT(NVARCHAR(10), @t_endtime, 120) + ' 9:00:00.00' AS DATETIME) END ELSE IF( Datepart(hh, @t_endtime) > 16 ) BEGIN --结束时间置于下午4点 SET @t_endtime=Cast(CONVERT(NVARCHAR(10), @t_endtime, 120) + ' 17:00:00.00' AS DATETIME) END --计算天数部分差值 SET @day_dvalue=Datediff(day, @t_starttime, @t_endtime) - 2 * Datediff(day, @t_starttime, @t_endtime) / 7 SET @day_dvalue=@day_dvalue + Datediff(day, @t_endtime, @endtime) - Datediff(day, @t_starttime, @starttime) --计算时间部分的差值(分钟) SET @minute_dvalue=( Datepart(hh, @t_endtime) - Datepart(hh, @t_starttime) ) * 60 + ( Datepart(mi, @t_endtime) - Datepart(mi, @t_starttime) ) --计算总时间差(分钟) SET @retrun_dvalue=@day_dvalue * 8 * 60 + @minute_dvalue END RETURN @retrun_dvalue
select dbo.fn_GetEffectiveWorkingTime('2013/2/4 12:21','2013/2/9 14:33')
结果:2199