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