How to update only days of a date in SQL Server

Question

How to update all days in date column in table to special date?

I want change only day, no month, no year

My table:

id    RegisterDate
------------------
1      2001-01-18
2      2018-09-13
3      1999-04-28
4      2012-12-15

The result that I expect:

id    RegisterDate
------------------
1      2001-01-02
2      2018-09-02
3      1999-04-02
4      2012-12-02

Answer 1

Probably the simplest way is to use datefromparts():

update t
    set RegisterDate = datefromparts(year(RegisterDate), month(RegisterDate), 2);

Another method that works in older versions of SQL Server is:

update t
     set RegisterDate = dateadd(day, 2 - day(RegisterDate), RegisterDate);

As a side node: there is no need to use string operations for this. The built-in date/time functions are quite sufficient.