/* 根据时间段,返回时间段的工作小时数:一天按8小时 */
CREATE FUNCTION [dbo].[fc_GetWorkHour] (@startdate smalldatetime,@enddate smalldatetime)
RETURNS int AS
BEGIN
declare @delayday int --暂用工时(日)
if(@startdate is null)
return 0
if(@enddate is null)
return 0
declare @tmptb table(dt smalldatetime)
declare @WeekendNum int
declare @s smalldatetime
set @s=@startdate
while @s<=convert(varchar(4),year(@enddate))+'-'+convert(varchar(2),month(@enddate))+'-'+convert(varchar(2),day(@enddate))+' 23:59'
begin
insert into @tmptb select @s set @s=dateadd(day,1,@s)
end --时间段内工作日个数
select @WeekendNum=count(*) from @tmptb where datepart(weekday,dt) not in (1,7)--工作个数(星期日为1,星期一为2,星期二为3....)
--开头,结尾是否是工作日,如果是的话,就除去
if(datepart(dw,@startdate)>1 and datepart(dw,@startdate)<7)
begin
set @WeekendNum=@WeekendNum-1
end
if(datepart(dw,@enddate)>1 and datepart(dw,@enddate)<7)
begin
set @WeekendNum=@WeekendNum-1
end
--计算整工作日小时数
set @delayday= 8*@WeekendNum*60
if(@delayday<0)
begin
set @delayday=0
end
if( datediff(dd,@startdate,@enddate)=0)--开始结束同一天
begin
set @delayday=datediff(mi,@startdate,@enddate)
end
else
begin
if(datepart(dw,@startdate)>1 and datepart(dw,@startdate)<7)
begin
if(datediff(mi,@startdate,convert(varchar(4),year(@startdate))+'-'+convert(varchar(2),month(@startdate))+'-'+convert(varchar(2),day(@startdate))+' 17:30')>0)
begin
set @delayday=@delayday+datediff(mi,@startdate,convert(varchar(4),year(@startdate))+'-'+convert(varchar(2),month(@startdate))+'-'+convert(varchar(2),day(@startdate))+' 17:30')
end
end
if(datepart(dw,@enddate)>1 and datepart(dw,@enddate)<7)
begin
if(datediff(mi,convert(varchar(4),year(@enddate))+'-'+convert(varchar(2),month(@enddate))+'-'+convert(varchar(2),day(@enddate))+' 08:30',@enddate)>0)
begin
set @delayday=@delayday+datediff(mi,convert(varchar(4),year(@enddate))+'-'+convert(varchar(2),month(@enddate))+'-'+convert(varchar(2),day(@enddate))+' 08:30',@enddate)
end
end
end
return @delayday END