CODEWITHSUNDEEP
sqlpostgresql
Qwertie
Qwertie

Reputation: 6573

Update only year in date field with postgres

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.

Upvotes: 13

Views: 10441

Answers (2)

clemens
clemens

Reputation: 17722

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.

Upvotes: 19

heiwil
heiwil

Reputation: 669

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.

Upvotes: 5

Related Questions