计算工作日函数

发布时间：2020-12-12 14:11:26 所属栏目：MsSql教程来源：网络整理

导读：计算工作日 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) %

计算工作日
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,@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,@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,
@workday = @i/7*5,
@dt_begin = DATEADD(day,
@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,@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,@date) return (@date) end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!