SET @Riqi=CAST(LTRIM(YEAR(GETDATE()))+'-'+LTRIM(@Month)+'-01' AS DATETIME);WITH AAA AS ( SELECT @Riqi AS RIQI UNION ALL SELECT DATEADD(DAY,1,AAA.RIQI) FROM AAA WHERE RIQI<DATEADD(DAY,-1,DATEADD(MONTH,1,@Riqi)) ) ,BBB AS ( SELECT RIQI, DATEPART(WEEKDAY,RIQI) AS DAYWEEK FROM AAA ) ,CCC AS ( SELECT ROW_NUMBER() OVER(ORDER BY RIQI) AS ROWINDEX, RIQI, DAYWEEK FROM BBB WHERE DAYWEEK NOT IN (6,7) ) SELECT RIQI FROM CCC WHERE ROWINDEX=@Day
自定义函数解决这个问题吧。 你还可以把节假日加进去。CREATE TABLE tb_Holiday( HDate smalldatetime primary key clustered, --节假日期 Name nvarchar(50) not null) --假日名称 GO --在指定日期上增加工作天数 CREATE FUNCTION f_WorkDayADD( @date datetime, --基础日期 @workday int --要增加的工作日数 )RETURNS datetime AS BEGIN IF @workday>0 WHILE @workday>0 SELECT @date=@date+@workday,@workday=count(*) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date+@workday ELSE WHILE @workday<0 SELECT @date=@date+@workday,@workday=-count(*) FROM tb_Holiday WHERE HDate BETWEEN @date AND @date+@workday RETURN(@date) END
感谢各位的回答,我还是参考gogodiy的回答,建立一个工作日期表,在进行判断。 以下是我的代码: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 */
这样一个函数或者过程应该怎样写呢?
这个貌似有点复杂,最好用C语言或C++写比较好。
SET DATEFIRST 1
DECLARE @Month INT, --月份
@Day INT, --第N个工作日
@Riqi DATETIME
SELECT @Month=3,@Day=11
SET @Riqi=CAST(LTRIM(YEAR(GETDATE()))+'-'+LTRIM(@Month)+'-01' AS DATETIME);WITH AAA AS
(
SELECT @Riqi AS RIQI
UNION ALL
SELECT DATEADD(DAY,1,AAA.RIQI)
FROM AAA
WHERE RIQI<DATEADD(DAY,-1,DATEADD(MONTH,1,@Riqi))
)
,BBB AS
(
SELECT RIQI,
DATEPART(WEEKDAY,RIQI) AS DAYWEEK
FROM AAA
)
,CCC AS
(
SELECT ROW_NUMBER() OVER(ORDER BY RIQI) AS ROWINDEX,
RIQI,
DAYWEEK
FROM BBB
WHERE DAYWEEK NOT IN (6,7)
)
SELECT RIQI
FROM CCC
WHERE ROWINDEX=@Day
你还可以把节假日加进去。CREATE TABLE tb_Holiday(
HDate smalldatetime primary key clustered, --节假日期
Name nvarchar(50) not null) --假日名称
GO
--在指定日期上增加工作天数
CREATE FUNCTION f_WorkDayADD(
@date datetime, --基础日期
@workday int --要增加的工作日数
)RETURNS datetime
AS
BEGIN
IF @workday>0
WHILE @workday>0
SELECT @date=@date+@workday,@workday=count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
ELSE
WHILE @workday<0
SELECT @date=@date+@workday,@workday=-count(*)
FROM tb_Holiday
WHERE HDate BETWEEN @date AND @date+@workday
RETURN(@date)
END
select @y = 2012, @m = 3, @workday = 10declare @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
根据1号是星期几也可以推算。
以下是我的代码: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
*/