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 IF @dt_begin> @dt_end SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt ELSE SET @bz=0 SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1, @workday=@i/7*5, @dt_begin=DATEADD(Day,@i/7*7,@dt_begin) 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
create function fn_test(@begin datetime,@end datetime) returns int As BEGIN declare @i int,@j int set @i=0 set @j=0 if @end> @begin begin while dateadd(d,@i,@begin) <=@end begin if datepart(weekday,dateadd(d,@i,@begin)) not in(1,7) set @j=@j+1 set @i=@i+1 end end
