Update only Date portion of DateTime

Question

I am using SQL Server 2008. I have a datetime in the database where the value is

    10/4/2012 8:03:00 AM

How do I update only the date portion?

The field name is dTime.

I like to update the date to 10/5/2012 and keep the time as it is.

Answer 1

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

Answer 2

UPDATE
    tableX
SET
    columnX = DATEADD( day, DATEDIFF( day, columnX, '20120510' ), columnX ) 
WHERE
    (conditionX) ;