Update datetime values to same date but persist time

Question

I'm in a situation where I would like to update the date of all the values in the table to have the same day but persist the time of each record.

So these three dates...
8/28/2012 14:00:00
8/28/2010 12:00:00
8/28/2008 10:00:00

Should be turned into...
8/28/2012 14:00:00
8/28/2012 12:00:00
8/28/2012 10:00:00

UPDATE MySpecialTable
SET DateField = {...?};

Edit: Regardless of the day the date is on (i.e. 8/28/2012, 1/1/2012, 4/1/2012, etc), I want to persist the time and mass update all the day/month/year to a specific value.

Answer 1

DECLARE @Target DATETIME;
SET @Target = '20120828';

-- if the date is supposed to be today rather than hard-coded or parameterized:

-- SET @Target = DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101');

UPDATE dbo.MySpecialTable
  SET DateField = DATEADD(DAY, DATEDIFF(DAY, DateField, @Target), DateField);

This will work if the target date is before or after the value in the column.

Also, if you only care about time, you should consider using the TIME data type (SQL 2008+). This will make it easy to apply any date you want to the data at query time, without having to actually perform any updates.