How to change only the year of a date datatype

Question

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.

Answer 1

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]   ); 

Answer 2

Here's the shortest I can think of, change the year to 2050:

select      dateadd(year, (2050 - year(d)), d)             from x 

Sample data:

create table x(d date);  insert into x values ('2001-11-19'), ('2020-07-05'), ('2012-05-01'); 

Output:

COLUMN_0 2050-11-19 2050-07-05 2050-05-01 

Live test: http://www.sqlfiddle.com/#!3/9a8b4/2