MSSQL 计算两个日期相差的工作天数的语句

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[f_workday]') and xtype in (n'fn', n'if', n'tf'))
drop function [dbo].[f_workday]
go
--计算两个日期相差的工作天数
create function f_workday(
@dt_begin datetime, --计算的开始日期
@dt_end datetime --计算的结束日期
)returns int
as
begin
declare @i int
select @i=abs(datediff(dd,@dt_begin,@dt_end))
declare @t table(dt datetime)
if @dt_begin>@dt_end
insert @t select dateadd(dd,number,@dt_end) from master..spt_values
where number<=@i and type='p'
else
insert @t select dateadd(dd,number,@dt_begin) from master..spt_values
where number<=@i and type='p'
return(select count(*) from @t where (datepart(weekday,dt)+@@datefirst-1)%7 between 1 and 5)
end
go
select dbo.f_workday('2009-10-10','2009-10-1')
/*
-----------
7
(1 個資料列受到影響)
*/
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[f_workday]') and xtype in (n'fn', n'if', n'tf'))
drop function [dbo].[f_workday]
go
--计算两个日期相差的工作天数
create function f_workday(
@dt_begin datetime, --计算的开始日期
@dt_end datetime --计算的结束日期
)returns int
as
begin
declare @workday int,@i int,@bz bit,@dt datetime
set @workday=0
if @dt_begin>@dt_end
select @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
else
set @bz=0
while @dt_begin<=@dt_end
begin
select @workday=case
when (@@datefirst+datepart(weekday,@dt_begin)-1)%7 between 1 and 5
then @workday+1 else @workday end,
@dt_begin=@dt_begin+1
end
return(case when @bz=1 then -@workday else @workday end)
end
go
select dbo.f_workday('2009-10-10','2009-10-1')
/*
-----------
-7
*/