我想仅在没有时间更改的情况下更新列 [英] I want to update a column only the date without the time change
本文介绍了我想仅在没有时间更改的情况下更新列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
hi
我想更新一列没有时间更改的日期
请求帮助我
解决方案
尝试这个:
更新
Yourtable
SET
datetimecol = DATEADD(day,DATEDIFF(day,datetimecol,getdate()),datetimecol)
如果你的意思是你有一个包含DateTime信息的列,并且想要更改日期部分并保持时间不变,那就太麻烦了,因为没有简单的方法可以做到这一点。
UPDATE MyTable SET myDateColumn = CONVERT( DATETIME ,' 2014-02-12')+ CAST(myDateColumn AS TIME )
CREATE TABLE dbo.MyTable (
ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,
DTColumn DATETIME NOT NULL
);
GO
INSERT dbo.MyTable(DTColumn) VALUES
(' 20120410 08:03:00.000' ),
(' 20010101 01:01:01.000');
BEGIN TRANSACTION ;
更新 dbo.MyTable
- 样式126 / ISO8601 = yyyy-mm-ddThh:mi:ss.mmm
SET DTColumn = STUFF( CONVERT ( VARCHAR ( 50 ),DTColumn, 126 ), 1 , 10 ,' 2012-05-10')
- 取消注释此行以查看旧值和新值
- OUTPUT已删除.DTColumn AS OldValue,inserted.DTColumn AS NewValue
WHERE CONVERT ( DATE ,DTCol umn)= ' 2012-04-10'
SELECT * FROM dbo.MyTable;
ROLLBACK ;
- COMMIT
hi I want to update a column only the date without the time change
pleas help me解决方案Try this:
UPDATE Yourtable SET datetimecol = DATEADD(day, DATEDIFF(day, datetimecol, getdate()), datetimecol)
If you mean that you have a column containing DateTime information, and you want to change the date portion and leave the time unchanged, that's quite cumbersome, because there is no easy way to do it.
UPDATE MyTable SET myDateColumn=CONVERT(DATETIME,'2014-02-12') + CAST(myDateColumn AS TIME)
CREATE TABLE dbo.MyTable( ID INT IDENTITY(1,1) PRIMARY KEY, DTColumn DATETIME NOT NULL ); GO INSERT dbo.MyTable(DTColumn) VALUES ('20120410 08:03:00.000'), ('20010101 01:01:01.000'); BEGIN TRANSACTION; UPDATE dbo.MyTable -- Style 126 / ISO8601 = yyyy-mm-ddThh:mi:ss.mmm SET DTColumn = STUFF(CONVERT(VARCHAR(50),DTColumn,126) ,1, 10, '2012-05-10') -- Uncomment this line to see the old and new values -- OUTPUT deleted.DTColumn AS OldValue, inserted.DTColumn AS NewValue WHERE CONVERT(DATE,DTColumn)='2012-04-10' SELECT * FROM dbo.MyTable; ROLLBACK; -- COMMIT
这篇关于我想仅在没有时间更改的情况下更新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文