sql循环替换
数据库表stu_Info表中有一列info_Time,怎么将前面的日期2012-11-22循环替换成2012-11-23啊? 谢谢
info_Time
2012-11-22 13:54:56.000
2012-11-22 13:57:12.000
2012-11-22 13:58:42.000
2012-11-22 13:58:48.000
2012-11-22 14:00:05.000
2012-11-22 14:03:14.000
2012-11-22 14:03:50.000
2012-11-22 09:48:26.000
2012-11-22 11:00:36.000
2012-11-22 11:00:36.000
[解决办法]
select info_time, dateadd(dd,1,info_time) from stu_Info
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-23 08:57:22
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([info_Time] datetime)
insert [test]
select '2012-11-22 13:54:56.000' union all
select '2012-11-22 13:57:12.000' union all
select '2012-11-22 13:58:42.000' union all
select '2012-11-22 13:58:48.000' union all
select '2012-11-22 14:00:05.000' union all
select '2012-11-22 14:03:14.000' union all
select '2012-11-22 14:03:50.000' union all
select '2012-11-22 09:48:26.000' union all
select '2012-11-22 11:00:36.000' union all
select '2012-11-22 11:00:36.000'
select * from [test]
go
--更新
update test
set [info_Time]=dateadd(dd,1,[info_Time])
--直接查询
select dateadd(dd,1,[info_Time]) as [info_Time] from test
/*
info_Time
-----------------------
2012-11-23 13:54:56.000
2012-11-23 13:57:12.000
2012-11-23 13:58:42.000
2012-11-23 13:58:48.000
2012-11-23 14:00:05.000
2012-11-23 14:03:14.000
2012-11-23 14:03:50.000
2012-11-23 09:48:26.000
2012-11-23 11:00:36.000
2012-11-23 11:00:36.000
(10 行受影响)
*/
若果是datetime类型
update stu_Info set info_Time=info_Time+1
如果是string,varchar类型
update stu_Info set info_Time=replace(info_Time,'2012-11-22','2012-11-23')
if object_id('[stu_info]') is not null drop table [stu_info]
go
create table [stu_info] (info_Time datetime)
insert into [stu_info]
select '2012-11-22 13:54:56.000' union all
select '2012-11-22 13:57:12.000' union all
select '2012-11-22 13:58:42.000' union all
select '2012-11-22 13:58:48.000' union all
select '2012-11-22 14:00:05.000' union all
select '2012-11-22 14:03:14.000' union all
select '2012-11-22 14:03:50.000' union all
select '2012-11-22 09:48:26.000' union all
select '2012-11-22 11:00:36.000' union all
select '2012-11-22 11:00:36.000'
select info_time, dateadd(dd,1,info_time) newdd from [stu_info]
/*
info_time newdd
----------------------- -----------------------
2012-11-22 13:54:56.000 2012-11-23 13:54:56.000
2012-11-22 13:57:12.000 2012-11-23 13:57:12.000
2012-11-22 13:58:42.000 2012-11-23 13:58:42.000
2012-11-22 13:58:48.000 2012-11-23 13:58:48.000
2012-11-22 14:00:05.000 2012-11-23 14:00:05.000
2012-11-22 14:03:14.000 2012-11-23 14:03:14.000
2012-11-22 14:03:50.000 2012-11-23 14:03:50.000
2012-11-22 09:48:26.000 2012-11-23 09:48:26.000
2012-11-22 11:00:36.000 2012-11-23 11:00:36.000
2012-11-22 11:00:36.000 2012-11-23 11:00:36.000
(10 行受影响)
*/
declare mycursor cursor for select info_time from stu_info
declare @ddate datetime
open mycursor
fetch next from mycursor into @ddate
while @@fetch_status=0
begin
if info_Time=convert(char(10),info_Time,120)
begin
set @ddate=dateadd(dd,1,info_Time)
update a set a.info_Time=@ddate from stu_info a
end
fetch next from mycursor into @ddate
close mycursor
deallocate mycursor
declare mycursor cursor for select info_time from stu_info
declare @ddate datetime
open mycursor
fetch next from mycursor into @ddate
while @@fetch_status=0
begin
if info_Time=convert(char(10),info_Time,120)
begin
set @ddate=dateadd(dd,1,info_Time)
update a set a.info_Time=@ddate from stu_info a
end
else
fetch next from mycursor into @ddate
end
fetch next from mycursor into @ddate
close mycursor
deallocate mycursor
declare mycursor cursor for select info_Time from stu_Info
declare @ddate datetime,@oldate datetime
open mycursor
fetch next from mycursor into @oldate
while @@fetch_status=0
begin
if convert(char(10),@oldate,120)='2012-11-22'
begin
select @ddate=dateadd(dd,1,@oldate)
update a set a.info_Time=@ddate from stu_Info a where a.info_Time=@oldate
end
fetch next from mycursor into @oldate
end
close mycursor
deallocate mycursor