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