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.

enter image description here

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

Are they each off by a specific number of years? If not, how do you know what the new value is? — zimdanen, Apr 30 '12 at 18:04
Why did you state the format yyyy-mm-dd? By any chance, is your field stored in varchar? — Michael Buen, Apr 30 '12 at 18:05
It would be helpful if you show your table structure and a few examples of incorrect dates and how you know what to correct them to. — Aaron Bertrand, Apr 30 '12 at 18:05
Try to paste your sample data here, click the **Text to DLL** : http://www.sqlfiddle.com/ — Michael Buen, Apr 30 '12 at 18:06
Try this query: `UPDATE MyTable SET DOB = DATEADD(YEAR, -2, DOB)` — Bogdan Sahlean, Apr 30 '12 at 18:15
So what is the right answer in that case? If you have Feb. 29 2012 and take away two years, SQL Server tells you that is Feb. 28 2010. Of course Feb. 29 wasn't a valid day in that year, so how much can you trust the source data and your presumed corrections to that data? — Aaron Bertrand, Apr 30 '12 at 18:38

Aaron Bertrand · Accepted Answer · 2020-02-27T14:13:23.947

52

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

edited Feb 27 '20 at 14:13

answered Apr 30 '12 at 18:03

Aaron Bertrand

272,866
37
466
490

Can you explain this a little, please? I assume the [fix] is the correct, year? – wootscootinboogie Apr 30 '12 at 18:05
I have no idea, you need to tell us how you know what to correct each date to. You say it is incorrect, but where is the correct source? – Aaron Bertrand Apr 30 '12 at 18:06
No, I haven't said it's incorrect, just for a little clarification on the jargon :) e.g. src.id (is myTable.id) – wootscootinboogie Apr 30 '12 at 18:07
1

You said in your question: `I have a list of birthdays and the year is the only part that is incorrect.` OK, show us some examples, show us how to fix them. Are they all 5 years off, 10 years off, or is each row off by a different amount, and how do you know what the correct year should be? SHOW US. Without details we're just stabbing. – Aaron Bertrand Apr 30 '12 at 18:09
This is what I was looking for. Danke. – wootscootinboogie Apr 30 '12 at 18:18
@AaronBertrand Can you please tell me how to set a specific year, i.e. I want to set year 2019 in all columns. – Half Blood Prince Feb 27 '20 at 09:08
@HalfBloodPrince See udpate. – Aaron Bertrand Feb 27 '20 at 14:13
@AaronBertrand Thanks for the update. Actually my column is of type 'TimeStamp'. So following query worked for me. ::: UPDATE table_name SET timestamp_column = ADDDATE(timestamp_column, INTERVAL (YEAR("2019-01-01") - YEAR(timestamp_column)) year) where YEAR(timestamp_column) > 2019;` – Half Blood Prince Mar 02 '20 at 06:08
@HalfBloodPrince Great, but this question was about Microsoft SQL Server. That syntax looks like some other platform. – Aaron Bertrand Mar 02 '20 at 12:37

score 27 · Answer 2 · answered Apr 30 '12 at 18:15

27

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

answered Apr 30 '12 at 18:15

Michael Buen

38,643
9
94
118

2

That's the request, change only the year – Michael Buen Apr 30 '12 at 18:18
If database is just a bit object-oriented, we can do this: `UPDATE x SET d.Year = 2050`. Can only dream :-) – Michael Buen Apr 30 '12 at 18:19
But change every individual date by the same number of years, not change every date to be the same year. And it's not object-oriented, it's SQL Server. – Aaron Bertrand Apr 30 '12 at 18:19
Ok, I see now what you are saying, the question is edited now. The exact requirement is in modified question :-) – Michael Buen Apr 30 '12 at 18:21
"And it's not object-oriented, it's SQL Server" <-- I know it's not object-oriented :-) I'm just pining for it, data could have more structure if it is object-oriented ;-) – Michael Buen Apr 30 '12 at 18:24
Don't hold your breath, there is going to be a lot of pushback for that, even though you do see some non-relational and semi-structured features creeping into SQL Server. – Aaron Bertrand Apr 30 '12 at 18:26
Ironically I found this QA looking for an example to change only the date's year to one specific year. Fortunately I found this answer in this similar question: http://stackoverflow.com/questions/10550368/updating-year-in-datetime-variable – Hagelt18 Jul 22 '15 at 17:11

score 5 · Answer 3 · edited Oct 10 '18 at 10:18

5

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

edited Oct 10 '18 at 10:18

WhatsThePoint

3,395
8
31
53

answered Sep 01 '14 at 05:20

Matt Welander

8,234
24
88
138

Can you please explain this? What is INTERVAL here. – Half Blood Prince Feb 27 '20 at 07:46

How to change only the year of a date datatype

3 Answers3