How to update time value?

Question

I have several datetime entries that I need to replaced the time part with the midnight value - '00:00:00.000'.

How do I update only the time part of a datetime value? The date needs to be retained.

Answer 1

The easiest way is to cast to a date and then back again:

select cast(cast(col as date) as datetime)

or

update t
    set col = cast(col as date);