Skip to content
Advertisement

How to change only the year of a date datatype

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.

enter image description here

Sample included in edit. Every year needs to be decreased by 2.

Advertisement

Answer

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]
  );
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement