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.

Answer 1

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.

Answer 2

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.