计算工作日函数

计算工作日
create function f_workday(
@dt_begin datetime,
@dt_end datetime
) returns int
as
begin
declare @workday int
set @workday = 0
while @dt_begin <= @dt_end
select @workday = case
when (DATEPART(weekday,@dt_begin) + @@DATEFIRST - 1) % 7 between 1 and 5
then @workday + 1 else @workday end,@dt_begin = DATEADD(day,1,@dt_begin)
return (@workday)
end


create function f_workday_new(
@dt_begin datetime,
@dt_end datetime
) returns int
as
begin
declare @workday int,@i int
select @i = DATEDIFF(day,@dt_begin,@dt_end) + 1,@workday = @i/7*5,@dt_begin = DATEADD(day,@i/7*7,@dt_begin)
while @dt_begin <= @dt_end
begin
select @workday = case
when (DATEPART(weekday,@dt_begin) + @@DATEFIRST - 1 ) % 7 between 1 and 5
then @workday + 1 else @workday end,
@dt_begin = @dt_begin + 1
end
return (@workday)
end


create function f_workday_sec(
@dt_begin datetime,
@dt_end datetime
) returns int
as
begin
declare @workday int,@i int,@bz bit,@dt datetime
if @dt_begin > @dt_end
select @bz = 1,@dt= @dt_begin,@dt_begin = @dt_end,@dt_end = @dt
else
set @bz = 0
select @i = DATEDIFF(day,@dt_begin,@dt_end) + 1,
@workday = @i/7*5,
@dt_begin = DATEADD(day,@i/7*7,@dt_begin)
while @dt_begin <= @dt_end
begin
select @workday = case
when (DATEPART(weekday,@dt_begin) + @@DATEFIRST - 1) % 7 between 1 and 5
then @workday + 1 else @workday end,
@dt_begin += 1
end
return (case when @bz = 1 then -@workday else @workday end)

end

/*指定日期加指定工作天数后的日期*/
create function f_workdayadd(
@date datetime,
@workday int
) returns datetime
as
begin
declare @bz int
select @bz = case when @workday < 0 then -1 else 1 end,
@date = DATEADD(Week,@workday/5,@date),
@workday = @workday % 5
while @workday <> 0 
select @date = DATEADD(day,@bz,@date),@workday = case when (DATEPART(weekday,@date) + @@DATEFIRST - 1) % 7 between 1 and 5
then @workday - @bz else @workday end


--避免处理后的日期停留在工作日上
while (DATEPART(weekday,@date) + @@DATEFIRST - 1) % 7 in (0, 6)
set @date = DATEADD(day,@bz,@date)
return (@date)
end