34,838
社区成员




declare @y int, @m int, @workday int
select @y = 2012, @m = 3, @workday = 10
declare @date datetime = ltrim(@y*10000+@m*100+1)
set @date-=1
while (@workday>0) select @date+=1, @workday-=sign((datepart(weekday,@date)+@@datefirst-1)%7%6)
select @date -- 2012-03-14 00:00:00.000
IF OBJECT_ID('fnWorkday') IS NOT NULL
DROP FUNCTION fnWorkday;
GO
CREATE FUNCTION fnWorkday
(@date INT ,
@n INT )
RETURNS DATETIME
AS
BEGIN
DECLARE @m INT ,--增长的天数
@d INT ,--当月的天数
@wdate VARCHAR(10), --工作日期
@tyw AS tyWorkday,
@workday DATETIME --需要返回的工作日期
SET @m=1;
SET @wdate=@date*100+@m;
SET @d=DATEDIFF(DAY,@wdate,DATEADD(MONTH,1,@wdate ));
WHILE @m<=@d
BEGIN
SET @wdate=@date*100+@m;
/*当日期不是周六、周日的时候插入到@tyw表*/
IF DATEPART(dw,@wdate) NOT IN (1,7)
BEGIN
INSERT INTO @tyw VALUES(@wdate);
END;
SET @m=@m+1;
END;
SELECT @workday=Workday FROM @tyw WHERE Id=@n;
RETURN @workday;
END;
--调用
SELECT dbo.fnWorkday(201204,10);
/*
(无列名)
2012-04-13 00:00:00.000
*/