How to change only the year of a date datatype

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

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


In case anyone else needs it for MySQL, here's the syntax:

UPDATE dbo.TableToUpdate 
SET    [Date Column] = DATE_ADD([Date Column], INTERVAL -2 year); 

Tags:

Sql

Sql Server