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

*/