----------------日期处理问题续-----------------------------------------------------------------------------

Question

节假日日期                                                  星期                id          
------------------------------------------------------ -------------------- ----------- 
2008-01-01 00:00:00.000                                星期二                  1
2008-02-07 00:00:00.000                                星期四                  2
2008-02-08 00:00:00.000                                星期五                  3
2008-02-09 00:00:00.000                                星期六                  4
2008-04-04 00:00:00.000                                星期五                  6
2008-05-01 00:00:00.000                                星期四                  7
2008-05-02 00:00:00.000                                星期五                  8
2008-05-03 00:00:00.000                                星期六                  9
2008-06-09 00:00:00.000                                星期一                  12
2008-09-13 00:00:00.000                                星期六                  13
2008-10-01 00:00:00.000                                星期三                  15
2008-10-02 00:00:00.000                                星期四                  16
2008-10-03 00:00:00.000                                星期五                  17
2008-10-04 00:00:00.000                                星期六                  18
2009-01-01 00:00:00.000                                星期四                  20
2009-01-26 00:00:00.000                                星期一                  22
2009-01-27 00:00:00.000                                星期二                  23
2009-01-28 00:00:00.000                                星期三                  24
2009-01-29 00:00:00.000                                星期四                  25
2009-01-30 00:00:00.000                                星期五                  26
2009-01-31 00:00:00.000                                星期六                  27
2009-04-04 00:00:00.000                                星期六                  28
2009-05-01 00:00:00.000                                星期五                  30
2009-05-02 00:00:00.000                                星期六                  31（所影响的行数为 24 行）以上是一些法定节假日的日期,周日减一天的问题已得到解决.现在要做的是凡是系统中有的日期字段,有这些日期的全部往前递增,直到不是周日以及和这些日期相同的日期.
应该明白什么意思吧?

Answer 1

上一贴地址:http://topic.csdn.net/u/20090428/17/50823762-4f25-4fd1-93aa-558f37113572.html比较急,希望高手人帮帮忙,小弟先谢了.

Answer 2

--适用sql2005
set nocount on
--节假日期临时表
declare @t table(dd datetime)
insert into @t select '2008-05-01'
insert into @t select '2008-05-02'
insert into @t select '2008-05-03'
insert into @t select '2008-06-09'
--待计算的表
declare @test table(dd datetime)
insert into @test select '2008-05-01'
insert into @test select '2008-05-03'
insert into @test select '2008-05-06'--节假日期临时表上附加延后的双休日
;with t1(dd) as 
(
select dd from @t  
union select dd+1 from @t where datepart(dw,dd+1) in(7,1)
union select dd+2 from @t where datepart(dw,dd+2) in(7,1)
),
--递归计算延后日期
t2(dd,dd1) as
(
select dd,dd+1 from t1 union all
select a.dd,b.dd1 from t1 a  join t2 b on a.dd+1=b.dd
),
--取得最终计算休息日临时表
t3 (dd,dd1) as
(
select dd,max(dd1) as dd1 from t2 group by dd
)
--测试结果
select a.dd 日期,isnull(b.dd1,a.dd) as 延后日期 from @test a left join t3 b on a.dd=b.ddset nocount off/*
日期                      延后日期
----------------------- -----------------------
2008-05-01 00:00:00.000 2008-05-05 00:00:00.000
2008-05-03 00:00:00.000 2008-05-05 00:00:00.000
2008-05-06 00:00:00.000 2008-05-06 00:00:00.000
*/

Answer 3

set nocount on
--节假日期临时表
declare @t table(dd datetime)
insert into @t select '2008-05-01'
insert into @t select '2008-05-02'
insert into @t select '2008-05-03'
insert into @t select '2008-06-09'
--待计算的表
declare @test table(dd datetime)
insert into @test select '2008-05-01'
insert into @test select '2008-05-03'
insert into @test select '2008-05-06'--节假日期临时表上附加延后的双休日declare @t1 table(dd datetime,dd1 datetime)
insert into @t1 
select dd,dd+1 from @t  
        union select dd+1,dd+2 from @t where datepart(dw,dd+1) in(7,1)
        union select dd+2,dd+3 from @t where datepart(dw,dd+2) in(7,1)while @@rowcount>0
begin
update a set dd1=b.dd1 from @t1 a,@t1 b where a.dd1=b.dd
end
select a.dd,isnull(b.dd1,a.dd) from @test a left join @t1 b on a.dd=b.dd/*
日期                      延后日期
----------------------- -----------------------
2008-05-01 00:00:00.000 2008-05-05 00:00:00.000
2008-05-03 00:00:00.000 2008-05-05 00:00:00.000
2008-05-06 00:00:00.000 2008-05-06 00:00:00.000
*/

Answer 4

最好能像http://topic.csdn.net/u/20090428/17/50823762-4f25-4fd1-93aa-558f37113572.html 这个贴子五楼那样做个批处理。

Answer 5

你就判断啊，如果日期在临时表里面，日期+1
然后在判断得到的这个日期值，如果还是节假日或者周日，日期再加1啊你就while，递归调用，知道返回false为止