人资要求能自动统计两个日期间工作日天数,编写了一段函数代码如下:
create function [dbo].[CalcWorkDay](@beginday datetime, @endday datetime)
returns int AS begin
declare @caldays int
declare @id int
select @caldays=0
while DATEDIFF(d, @beginday, @endday) >= 0
begin
if datepart(dw,@beginday) > 1 and datepart(dw,@beginday) < 7
begin
select @caldays=@caldays+1
end
select @beginday=dateadd(day,1,@beginday)
end
return @caldays
end
调用方法:
select dbo.CalcWorkDay(SYSDATETIME(),dateadd(day,3,SYSDATETIME()))
当前日期是11.29,得到结果:3