How to change only the year of a date datatype

sql, sql-server

Solution

If all rows need to be decreased by two years, then:

UPDATE dbo.TableToUpdate
  SET [Date Column] = DATEADD(YEAR, -2, [Date Column]);

If all rows need to be set to a specific year (say, 2019), and the column is `date`:

UPDATE dbo.TableToUpdate
  SET [Date Column] = DATEFROMPARTS(2019, MONTH([Date Column]), DAY([Date Column]);

If all rows need to be set to a specific year (say, 2019) and the column is not `date`, you could use `DATETIMEFROMPARTS` or `SMALLDATETIMEFROMPARTS`, but at that point the following becomes shorter:

UPDATE dbo.TableToUpdate
  SET [Date Column] = DATEADD
  (
    YEAR, 
    -DATEDIFF(YEAR, '20190101', [Date Column]), 
    [Date Column]
  );

Problem

I have a list of birthdays and the year is the only part that is incorrect. I have a list of ID #s for these individuals. Is there a way to change only the year for all of these people? I was thinking something like making a table of the query results and then using an `UPDATE` `SET` query, but I don't know how to only change the year. Sample included in edit. Every year needs to be decreased by 2.

Original source

Related problems