create table a(kaiyongshijian datetime , shedingshijian int ,ID int , beizhu varchar(100))
insert into a
select '2007-4-3 12:00:00 ',12,1, ' ' union all
select '2007-4-3 12:00:00 ',15,5, ' ' union all
select '2007-4-3 12:00:00 ',20,9, ' ' union all
select '2007-5-3 12:00:00 ',10,2, ' ' union all
select '2007-5-3 12:00:00 ',7,3, ' ' union all
select '2007-5-3 12:00:00 ',5,6, ' '
declare @n int, @year int
set @year =2008 --設置年份
set @n=0
while @@rowcount> 0
begin
set @[email protected]+1
insert into b
select dateadd(month,[email protected], kaiyongshijian ),ID,beizhu
from a
where year(dateadd(month,[email protected], kaiyongshijian) )[email protected]
end
为什么只能算出来2008年的?别的年份都算不出
------解决方案--------------------
這麼用倒是可以
create table a(kaiyongshijian datetime , shedingshijian int ,ID int , beizhu varchar(100))
insert into a
select '2007-4-3 12:00:00 ',12,1, ' ' union all
select '2007-4-3 12:00:00 ',15,5, ' ' union all
select '2007-4-3 12:00:00 ',20,9, ' ' union all
select '2007-5-3 12:00:00 ',10,2, ' ' union all
select '2007-5-3 12:00:00 ',7,3, ' ' union all
select '2007-5-3 12:00:00 ',5,6, ' '
create table b(kaiyongshijian datetime , ID int , beizhu varchar(100))
declare @n int, @year int
set @year =2009 --設置年份
set @n=0
--while @@rowcount> 0
while @n <= 100
begin
set @[email protected]+1
insert into b
select dateadd(month,[email protected], kaiyongshijian ),ID,beizhu
from a
where year(dateadd(month,[email protected], kaiyongshijian) )[email protected]
end
Select * From b
Drop Table a,b