sql server 2008 - Update only time from my Datetime field in sql

Question

I have a date 2013-12-14 05:00:00.000 in my table.

Now i want to update only time of that date. E.G to 2013-12-14 04:00:00.000

Is there any query to update only time from datetime field?

Answer 1

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(CAST(MyDate AS DATE) AS DATETIME)) 

Or this

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(FLOOR(CAST(MyDate AS FLOAT)) AS DATETIME))