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('2011-5-1','2011-5-7') '工作天数'