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