How to add hours, minutes and seconds to a datetime column in SQL?

Question

I have the following datetime column in my table "UpdatedTime"

Sample value: 2021-12-31 00:00:00.000

I need to manipulate the hours, minutes and seconds to become 23:59:59 (i.e. a second before midnight.)

Expected Value: 2021-12-21 23:59:59.000

Thanks in advance

Answer 1

I would use dateadd(), but I would phrase it as:

select dateadd(second, 24 * 60 * 60 - 1, UpdatedTime)

Or just add a time value:

select UpdatedTime + '23:59:59'