Update only Date portion of DateTime
UPDATE table1
SET columnDatetime = '10/5/2012' + ' ' + CONVERT(varchar(12), CONVERT(time, columnDatetime))
WHERE CONVERT(date, columnDatetime) = '10/4/2012'
Just use varchar and modify what you want in it without touch the time. In this example I use CONVERT(varchar(12), columnDatetime) to get a string with length of 12 characteres assuming a case of time with a format for example like "20:10:15.250".
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
Results:
OldValue NewValue
----------------------- -----------------------
2012-04-10 08:03:00.000 2012-05-10 08:03:00.000
ID DTColumn
----------- -----------------------
1 2012-05-10 08:03:00.000
2 2001-01-01 01:01:01.000
UPDATE
tableX
SET
columnX = DATEADD( day, DATEDIFF( day, columnX, '20120510' ), columnX )
WHERE
(conditionX) ;
UPDATE YourTable
SET YourColumn = DATEADD(MONTH,1,YourColumn)
WHERE YourColumn = '20120410 08:03:00'
I assumed that you posted your date on DD/MM/YYYY
format. If it was MM/DD/YYYY
you should use DATEADD(DAY,1,YourColumn)
instead.