有一个表名叫txslsz 我需要把其中一列data的内容的日期修改,但是后面的时间保持不变,注:该日期格式不是文本格式,请问修改语句如何写?data2008-09-10 18:20:31
2008-09-10 18:22:34
2008-09-10 18:23:37
2008-09-10 18:25:31
.......
要修改为:data2008-09-20 18:20:31
2008-09-20 18:22:34
2008-09-20 18:23:37
2008-09-20 18:25:31
.......
2008-09-10 18:22:34
2008-09-10 18:23:37
2008-09-10 18:25:31
.......
要修改为:data2008-09-20 18:20:31
2008-09-20 18:22:34
2008-09-20 18:23:37
2008-09-20 18:25:31
.......
insert @t select '2008-09-10 18:20:31'
union all select '2008-09-10 18:22:34'
union all select '2008-09-10 18:23:37'
union all select '2008-09-10 18:25:31' select dateadd(dd,10,data) from @tselect data + 10 from @t
if object_id('T')is not null
drop table T
go
create table T
( data datetime)
insert into T select '2008-09-10 18:20:31'
union all select '2008-09-10 18:22:34'
union all select '2008-09-10 18:23:37'
union all select '2008-09-10 18:25:31'----
update T set data=dateadd(day,10,data)
------
select * from T
Set Nocount On
declare @1 table([data] nvarchar(50))
Insert @1
select '2008-09-10 18:20:31' union all
select '2008-09-10 18:22:34' union all
select '2008-09-10 18:23:37' union all
select '2008-09-10 18:25:31'
Select * from @1
Update @1
Set data=Convert(nchar(19),Convert(datetime,data)+10,120 )
Select * from @1/*
data
--------------------------------------------------
2008-09-10 18:20:31
2008-09-10 18:22:34
2008-09-10 18:23:37
2008-09-10 18:25:31data
--------------------------------------------------
2008-09-20 18:20:31
2008-09-20 18:22:34
2008-09-20 18:23:37
2008-09-20 18:25:31*/
在向指定日期加上一段时间的基础上,返回新的 datetime 值。语法
DATEADD ( datepart , number, date ) 参数
datepart是规定应向日期的哪一部分返回新值的参数。下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。日期部分 缩写
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s