Update only year in date field with postgres

Question

I have a column with the type date and I want to update only the year in the dates while leaving the day and month as they are. I want to set the year to a specific value regardless of what the current value is. The answers currently on stack overflow involve adding or subtracting years from the date which doesn't seem to be what I need.

you can use your accepted answer, but it may be error one day. because if your current date is 29 february 2024, plus 1 year will become 29 february 2025, which is invalid date. @helwil's answer is safer. — Jacky Supit
– Jacky Supit, Commented Sep 23, 2024 at 10:26

clemens · Accepted Answer · 2019-05-23 13:42:37Z

19

You can set a specific year like this:

UPDATE my_table SET date_column = date_column + 
    MAKE_INTERVAL(YEARS := ***year*** - EXTRACT(YEAR FROM date_column)::INTEGER)

where ***year***is the specific year as integer. For instance

UPDATE my_table SET date_column = date_column + 
    MAKE_INTERVAL(YEARS := 2001 - EXTRACT(YEAR FROM date_column)::INTEGER)

will set the year of all dates to 2001.

edited May 23, 2019 at 13:42

answered May 23, 2019 at 13:34

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

heiwil · Accepted Answer · 2019-05-23 13:31:27Z

5

You can do it like this:

UPDATE yourTable SET date_field = date_field + interval '1 year';

Edit:

But that's what you find also here on stackoverflow. What exactly is not working for you? This statement takes the date, adds one year on it and saves it back to database.

answered May 23, 2019 at 13:31

heiwil

6594 silver badges9 bronze badges

2 Comments

Qwertie Over a year ago

This doesn't work for me because it requires that I know the amount of time I want to add/subtract while writing the sql when I actually want to set every date to a specific year.

gdibble Over a year ago

This was perfect for me because I wanted to increment by a set number. Thanks for posting this tip. 🙌

Collectives™ on Stack Overflow

Update only year in date field with postgres

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related