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

• Posted by Admin on 十月 4, 2009
• 0 comments

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
*/

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/09/29/4616501.aspx