SQL Server 2005 计算工作日函数

zz775520666

由于工作需要，在SQL Server 2005 下面写了一个计算两个日期之间相差工作日的函数。函数是以一个星期5天工作日计算，没有剔除五一国庆等假期。代码如下：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Sinmen
-- Create date: 2007-11-01
-- Description: 计算两个日期之间相差的工作日
-- =============================================
ALTER FUNCTION [dbo].[WorkDatediff]
(
@begin_date datetime,
@end_date datetime
)
RETURNS int
AS
BEGIN
DECLARE @return_date_quantity int
DECLARE @temp datetime
DECLARE @week_quantity int
DECLARE @day_quantity int
DECLARE @begin_day_of_week int
DECLARE @end_day_of_week int
DECLARE @add_begin_day_quantity int
DECLARE @add_end_day_quantity int
set @day_quantity = Datediff(d,@begin_date,@end_date)
--判断传入的开始日期是否比结束日期大
if @day_quantity < 0
  begin
   set @temp = @begin_date
   set @begin_date = @end_date
   set @end_date = @temp
  end
set @week_quantity = Abs(Datediff(ww,@begin_date,@end_date)) - 1
if @week_quantity < 0
  set @week_quantity = 0
set @begin_day_of_week = Datepart(dw,@begin_date) - 1
set @end_day_of_week = Datepart(dw,@end_date) - 1

set @add_begin_day_quantity = case
         when @begin_day_of_week > 5 then 0
         else 6 - @begin_day_of_week --(5 - @begin_day_of_week + 1)
        end
set @add_end_day_quantity = case
         when abs(@day_quantity) < 8  then 0
         when @end_day_of_week > 5 then 5
         else @end_day_of_week
        end
if @day_quantity = 0
  set @return_date_quantity = 0
else
  set @return_date_quantity = @week_quantity * 5 + @add_begin_day_quantity + @add_end_day_quantity
if @day_quantity < 0
  set @return_date_quantity = @return_date_quantity * -1

RETURN @return_date_quantity
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO