SQL里dateadd计算日期时跳过周六周日两天计算日期
比如
DATEADD(D,7,'2015-04-01') 应该是 2015-04-08, 但是我希望取到的是2015-04-10
DATEADD(D,14,'2015-04-01') 应该是 2015-04-15, 但是我希望取到的是2015-04-21
------解决思路----------------------
如果增加的天数不是特别多，可以试试这句：
declare @cdate varchar(20)
declare @i int
set @cdate='2015/04/01'
set @i=14
select MAX(cdate) from (
select *, ROW_NUMBER() over(order by cdate) id
from (select dateadd(dd,number, CONVERT(datetime, @cdate)) cdate from spt_values where type='P' and number<@i*2) aa
where DATEPART(dw,cdate)<>1 and DATEPART(DW,cdate)<>7
)bb where id<=@i+1
------解决思路----------------------
-- 自定义dateadd函数,跳过周六周日.
create function dbo.dateadd2
(@date0 datetime,
@x int)
returns datetime
as
begin
declare @i int,@date1 datetime
select @i=1,@date1=@date0
while(@i<=@x)
begin
select @date1=dateadd(d,1,@date1)
if datepart(dw,@date1) not in(1,7)
begin
select @i=@i+1
end
end
return @date1
end
-- 测试1
select dateadd(D,7,'2015-04-01') 'dateadd',dbo.dateadd2('2015-04-01',7) 'dateadd2'
/*
dateadd dateadd2
----------------------- -----------------------
2015-04-08 00:00:00.000 2015-04-10 00:00:00.000
(1 row(s) affected)
*/
-- 测试2
select dateadd(D,14,'2015-04-01') 'dateadd',dbo.dateadd2('2015-04-01',14) 'dateadd2'
/*
dateadd dateadd2
----------------------- -----------------------
2015-04-15 00:00:00.000 2015-04-21 00:00:00.000
(1 row(s) affected)
*/
------解决思路----------------------
修改一下语句，主要是增加了spt_values表的数据库。
declare @cdate varchar(20)
declare @i int
set @cdate='2015/04/01'
set @i=14
select MAX(cdate) from (
select *, ROW_NUMBER() over(order by cdate) id
from (select dateadd(dd,number, CONVERT(datetime, @cdate)) cdate from master..spt_values where type='P' and number<@i*2) aa
where DATEPART(dw,cdate)<>1 and DATEPART(DW,cdate)<>7
)bb where id<=@i+1
------解决思路----------------------
IF OBJECT_ID('dbo.DateAddWorkdays') IS NOT NULL
DROP FUNCTION DateAddWorkdays
GO
-- 求 @date 之后的第 @workdays 个工作日
CREATE FUNCTION DateAddWorkdays(@workdays int, @date datetime)
RETURNS datetime
AS
BEGIN
-- 必须调用 SET DATEFIRST 1 保证休日判断的正确
IF @@DATEFIRST <> 1
RETURN NULL
DECLARE @weekday int
DECLARE @weeks int
DECLARE @days int
DECLARE @fulldays int
SET @weekday = DatePart(weekday, @date) -- 起始日星期几？
IF @weekday = 5 -- 周五多经过2个休日
SET @fulldays = 2
ELSE IF @weekday = 6 -- 周六多经过1个休日
SET @fulldays = 1
ELSE
SET @fulldays = 0 -- 其它：第二天就是工作日
IF @weekday >= 5 -- 周五、六、日都要从下周开始第一个工作日
SET @weekday = 0 -- 用0表示方便和 @days 相加
SET @weeks = @workdays / 5 -- 整周数
SET @days = @workdays % 5 -- 不足一周的工作日数
SET @fulldays = @fulldays + @weeks * 7 -- 经过整周的天数
IF (@weekday + @days) < 6 -- 可以结束于周六前
SET @fulldays = @fulldays + @days
ELSE -- 否则再多2个休日
SET @fulldays = @fulldays + @days + 2
RETURN DateAdd(day, @fulldays, @date)
END
GOSET DATEFIRST 1
SELECT dt day0,
DatePart(weekday,dt) weekday,
CASE WHEN DatePart(weekday,dt) <=5 THEN
1
ELSE
0
END workday,
dbo.DateAddWorkdays(7, dt) day7,
dbo.DateAddWorkdays(14, dt) day14
FROM (
SELECT DateAdd(day,number,'2015-04-01') dt
FROM master..spt_values
WHERE type = 'p'
AND number < 30
) t
day0 weekday workday day7 day14
---------- ----------- ----------- ---------- ----------
2015-04-01 3 1 2015-04-10 2015-04-21
2015-04-02 4 1 2015-04-13 2015-04-22
2015-04-03 5 1 2015-04-14 2015-04-23
2015-04-04 6 0 2015-04-14 2015-04-23
2015-04-05 7 0 2015-04-14 2015-04-23
2015-04-06 1 1 2015-04-15 2015-04-24
2015-04-07 2 1 2015-04-16 2015-04-27
2015-04-08 3 1 2015-04-17 2015-04-28
2015-04-09 4 1 2015-04-20 2015-04-29
2015-04-10 5 1 2015-04-21 2015-04-30
2015-04-11 6 0 2015-04-21 2015-04-30
2015-04-12 7 0 2015-04-21 2015-04-30
2015-04-13 1 1 2015-04-22 2015-05-01
2015-04-14 2 1 2015-04-23 2015-05-04
2015-04-15 3 1 2015-04-24 2015-05-05
2015-04-16 4 1 2015-04-27 2015-05-06
2015-04-17 5 1 2015-04-28 2015-05-07
2015-04-18 6 0 2015-04-28 2015-05-07
2015-04-19 7 0 2015-04-28 2015-05-07
2015-04-20 1 1 2015-04-29 2015-05-08
2015-04-21 2 1 2015-04-30 2015-05-11
2015-04-22 3 1 2015-05-01 2015-05-12
2015-04-23 4 1 2015-05-04 2015-05-13
2015-04-24 5 1 2015-05-05 2015-05-14
2015-04-25 6 0 2015-05-05 2015-05-14
2015-04-26 7 0 2015-05-05 2015-05-14
2015-04-27 1 1 2015-05-06 2015-05-15
2015-04-28 2 1 2015-05-07 2015-05-18
2015-04-29 3 1 2015-05-08 2015-05-19
2015-04-30 4 1 2015-05-11 2015-05-20