实现指定时间段的工作天数

--实现指定时间段的工作天数 create function dbo.FC_WorkDay(@StartTime datetime,@EndTime datetime) returns int as begin declare @workdays int declare @weeks int --工作的完整周数 select @weeks=(datediff(day,@StartTime,@EndTime)+1)/7, --@weeks周内工作的天数 @workdays=@weeks*5, --最后一个不完整周的开始天数 @StartTime=dateadd(day,@weeks*7,@StartTime) while @StartTime<=@EndTime begin select @workdays =case when (@@datefirst+datepart(weekday,@StartTime)-1)%7 between 1 and 5 then @workdays+1 else @workdays end,@StartTime=@StartTime+1 end return(@workdays) end select dbo.FC_WorkDay('2009-1-1','2009-2-14') '工作天数' 工作天数 ----------- 32 (1 行受影响) --FC提供的代码 declare @d1 datetime,@d2 datetime select @d1='2009-1-1',@d2='2009-2-14' select datediff(wk,dateadd(dd,-datepart(dw,@d1)+1,@d1), dateadd(dd,-datepart(dw,@d2)+1,@d2)) * 5 + (datepart(dw,@d2)-1) - (datepart(dw,@d1)-1) --疯子提供的代码 declare @s datetime,@e datetime,@d int select @s='2009-1-1',@e='2009-2-14',@d=datediff(dd,@s,@e) ;with date as ( select @s as dt union all select dt+1 from date where dt+1<=@e ) select count(*) as '工作天数' from date where (datepart(dw,dt)+@@datefirst-1)%7 not in(0,6)