仅在 SQL Server 中更新日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20421521/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update date only in SQL Server
提问by user3073987
I have this row in my database table with a value of 1/5/2013 5:50:00 PM, and I want to update only the date part. Time should be same without any change, need to change only the date in this record.
我的数据库表中有这一行，值为 1/5/2013 5:50:00 PM，我只想更新日期部分。时间应该是一样的，没有任何变化，只需要改变这个记录中的日期。
I have tried the update statement but it change the time as well..but I can do a
我已经尝试过更新语句，但它也改变了时间......但我可以做一个
UPDATE table1
SET date = '1/10/2013 5:50:00 PM'
WHERE id =1
This not what I'm looking for, different id's have different times, so just need to update the date keeping the time in that record same.
这不是我要找的，不同的 id 有不同的时间，所以只需要更新日期，保持该记录中的时间相同。
Please give feedback.
请给予反馈。
Thank you
谢谢
回答by Raj
UPDATE table1
SET date = DATEADD(dd,5,date)-- 5 is the number of days
FROM table1
WHERE id =1
回答by Szymon
You can do it this way if you're using SQL Server 2008 or higher
如果您使用的是 SQL Server 2008 或更高版本，则可以这样做
UPDATE table1
SET [date] = cast('1/10/2013' as datetime) + cast(cast([date] as time) as datetime)
WHERE id =1
If you're using SQL Server 2005 or below, you there's no timedata type, so you have to do:
如果您使用的是 SQL Server 2005 或更低版本，则没有time数据类型，因此您必须执行以下操作：
UPDATE table1
SET [date] = cast('1/10/2013' as datetime) + ([date] - DATEADD(dd, 0, DATEDIFF(dd, 0, [date])))
WHERE id =1
回答by Panu Oksala
Not the most elegant, but should work. Idea is to extract hours,minutes and seconds from target row and hard code other parts.
不是最优雅的，但应该工作。想法是从目标行中提取小时、分钟和秒，并对其他部分进行硬编码。
update table1 set date = DATEADD(second, DATEPART(second, date), DATEADD(minute, DATEPART(minute, date), DATEADD(hour, DATEPART(hour,date), '2013-01-10')));
回答by Prahlad Yeri
Though a bit hackish in appearance, this will do the trick if you ONLY want to change the date part with an arbitary value, and not touch the time part at all:
虽然在外观上有点 hackish，但如果您只想使用任意值更改日期部分，而根本不触摸时间部分，这将起作用：
update table1 set date= '06-dec-2013 ' + cast(datepart(HOUR,date) as varchar(2)) + ':' +
cast(datepart(MINUTE,date) as varchar(2)) + ':' +
cast(DATEPART(SECOND, date) as varchar(2)) + ' ' +
CASE WHEN DATEPART(HOUR, date) < 12 THEN 'AM' ELSE 'PM' END as datetime
where id=1
Replace '06-dec-2013' with the date value you want to replace with.
将 '06-dec-2013' 替换为要替换的日期值。
回答by vhadalgi
create table t
(
col1 datetime
)
Insert Into t
values ('1/5/2013 5:50:00 PM')
declare @newDate datetime
set @newDate = '1/10/2013'
update t
Set col1 = convert(datetime,DateAdd(day, DateDiff(day, col1, @newDate), Col1),101)
sql-fiddle demo
sql-fiddle demo
回答by Damien_The_Unbeliever
Here's the query you want:
这是您想要的查询：
declare @TargetDate datetime
set @TargetDate = '20131001' --1st October 2013
update table1 set [date] = DATEADD(day,DATEDIFF(day,[date],@TargetDate),[date])
where id = 1
And here's a complete script to demonstrate it:
这是一个完整的脚本来演示它：
declare @t table (dt datetime not null)
insert into @t (dt) values
('2001-01-01T10:53:44.993'),('2012-06-18T15:33:33.333')
declare @TargetDate datetime
set @TargetDate = '20131001' --1st October 2013
update @t set dt = DATEADD(day,DATEDIFF(day,dt,@TargetDate),dt)
select * from @t
Results:
结果：
dt
-----------------------
2013-10-01 10:53:44.993
2013-10-01 15:33:33.333
This works by using DATEDIFFto work out how many days different the target date is from each date stored in the table (with appropriate signage) and then adding that difference back onto the date - having the effect of adjusting the date portions of the stored values whilst not affecting the time.
这通过使用DATEDIFF来计算目标日期与存储在表中的每个日期（带有适当的标志）不同的天数，然后将该差异添加回日期 - 具有调整存储值的日期部分的效果，同时不影响时间。
