sql日期累加函数（跳过非工作时间）

sql日期工作函数：
描述：从某个时间点starttime开始工作，工作时长n小时，得到工作结束日期。条件：每日有效工作日期为上午8点-12点，下午14点-18点。非工作时间跳过不计入工作时长。
创建函数sql语句如下：

GO

/****** Object:  UserDefinedFunction [dbo].[dateAddWork]    Script Date: 2020-01-18 10:42:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--select top(50) * from PDM_WOMFGOrder order by enterdate desc
--select dateadd(second,2*60*60,'2020-01-06 12:10:13.577')
CREATE function [dbo].[dateAddWork] (@datex datetime,@t int)
returns datetime as
begin
declare @date datetime
set @date = @datex--传入的时间
declare @dates datetime --传入时间的当天的开始时间
set @dates =  SUBSTRING(CONVERT(CHAR(23), @date, 121),0,11)  
declare @hour int
declare @day int
declare @astart int
declare @as datetime
declare @aend int
declare @ae datetime
declare @atimes int
declare @pstart int
declare @ps datetime
declare @pend int
declare @pe datetime
declare @btimes int
declare @end int
declare @ee datetime
declare @vt datetime
set @astart = 8*60*60 --上午开始工作时间的时长（秒）
set @as =  dateadd(second,@astart,@dates)
set @aend = 12*60*60  --上午结束工作时间的时长（秒）
set @ae =  dateadd(second,@aend,@dates)
set @pstart = 14*60*60--下午开始工作时间的时长（秒）
set @ps =  dateadd(second,@pstart,@dates)
set @pend = 18*60*60  --下午结束工作时间的时长（秒）
set @pe =  dateadd(second,@pend,@dates)
set @end = 24*60*60   --一天结束时间时长（秒）
set @ee =  dateadd(second,@end,@dates)
--select @dates
set @atimes= @aend -@astart --上午工作时长（秒）
set @btimes= @pend - @pstart --下午工作时长（秒）
set @hour =@t%((@atimes+@btimes)/60/60)*60*60
set @day = @t / ((@atimes+@btimes)/60/60) --工作时长/每日有效工作时长 得带需要的工作天数
--select @date as '@date',@dates  as '@dates',@as  as '@as',@ae  as '@ae',@ps  as '@ps',@pe  as '@pe',@ee  as '@@ee'
if @date  <=@as
	begin 
		--select 传入的起始日期在0-8点
		if @hour<=@atimes
		 set @vt = DATEADD(second,@hour,@as)
		else
		set @vt = DATEADD(second,@hour-@atimes,@ps)
	end
else if @date>@as and @date <=@ae
	begin 
		--select 传入的起始日期在8-12点
		if datediff(second,@date,@ae) >= @hour
		 set @vt = DATEADD(second,@hour,@date) 
		else 
		 begin 
		  if datediff(second,@date,@ae)+@btimes >= @hour
		  set @vt = DATEADD(second,@hour-datediff(second,@date,@ae),@ps)
		  else
		   set @vt = DATEADD(second,@hour-datediff(second,@date,@ae)-@btimes,DATEADD(second,@astart,@ee))
		 end
	end
else if @date>@ae and @date <=@ps
	begin 
		-- 传入的起始日期在12-14点'
		if @hour<=@btimes
		 set @vt = DATEADD(second,@hour,@ps)
		 else
		  set @vt = DATEADD(second,@hour-@btimes,DATEADD(second,@astart,@ee))
	end
else if @date>@ps and @date <=@pe
	begin 
		-- 传入的起始日期在14-18点'
		if datediff(second,@date,@pe) >= @hour
		 set @vt = DATEADD(second,@hour,@date)
		else
		begin 
			if datediff(second,@date,@pe)+@atimes >= @hour
		  set @vt = DATEADD(second,@hour-datediff(second,@date,@pe),DATEADD(second,@astart,@ee))
		  else
		   set @vt = DATEADD(second,@hour-datediff(second,@date,@pe)-@atimes,DATEADD(second,@pstart,@ee))
		end
	end 
else 
	begin 
		-- 传入的起始日期在18-24点'
		if @hour<=@atimes
				begin 
				 set @vt = DATEADD(second,@hour,DATEADD(second,@astart,@ee))
				end
		else
		set @vt = DATEADD(second,@hour-@atimes,DATEADD(second,@pstart,@ee))
	end 
--select @vt
 return  dateadd(day,@day,@vt)
end

GO

函数实现思想：
1）对于工作时长小于等于每日有效工作时长（这里是8小时）的情况。传入的起始时间可能有5种情况，分别是传入的日期在0-8点，8-12点，12-14点，14-18点，18-24点。然后没每种情况计算得到结束工作日期。
2）对于工作时长大于每日有效工作时长的情况，可以用工作时长除以每日有效工作时长，得到天数和余数时长；然后利用1）中的方法根据开始工作时间和余数工作时长得到一个时间，再把这个时间加上工作天数，得到最终工作截止时间。
工作时长1-7小时的测试

declare  @adate datetime
declare  @h int
set @adate  = '2020-01-18 09:43:37' 
set @h=0
select @adate as [开始工作时间],(1+@h*8) as [工作时长],dbo.dateAddWork(@adate,1+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(2+@h*8) as [工作时长],dbo.dateAddWork(@adate,2+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(3+@h*8) as [工作时长],dbo.dateAddWork(@adate,3+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(4+@h*8) as [工作时长],dbo.dateAddWork(@adate,4+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(5+@h*8) as [工作时长],dbo.dateAddWork(@adate,5+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(6+@h*8) as [工作时长],dbo.dateAddWork(@adate,6+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(7+@h*8) as [工作时长],dbo.dateAddWork(@adate,7+@h*8) as [结束工作时间]

测试结果：

工作时长17-23小时测试：

declare  @adate datetime
declare  @h int
set @adate  = '2020-01-18 15:43:37' 
set @h=2
select @adate as [开始工作时间],(1+@h*8) as [工作时长],dbo.dateAddWork(@adate,1+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(2+@h*8) as [工作时长],dbo.dateAddWork(@adate,2+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(3+@h*8) as [工作时长],dbo.dateAddWork(@adate,3+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(4+@h*8) as [工作时长],dbo.dateAddWork(@adate,4+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(5+@h*8) as [工作时长],dbo.dateAddWork(@adate,5+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(6+@h*8) as [工作时长],dbo.dateAddWork(@adate,6+@h*8) as [结束工作时间]
select @adate as [开始工作时间],(7+@h*8) as [工作时长],dbo.dateAddWork(@adate,7+@h*8) as [结束工作时间]

测试结果：

更多测试结果可以修改@adate 的值和@h的值测试。

bug：如果得到的工作截止时间为12点正和18点正，则会变成14点正和下一天的早晨8点正，暂时还没有想到怎么处理。