MYSql change only year from timestamp

Question

Accidentally, my host had set the time for 2012 and in my database, there are more than 4000 records updated timestamp column as 2012-11-21 11:24:40.

I cant change time on all columns while updating, I need to keep the same time as per the table column, I just need to change only year 2012 to 2016 in that table.

I listed all columns using this query,

SELECT * FROM `table` WHERE `del_date` LIKE '%2012-11-21%';

but do not know to change the year only in that column.

Please help how can I change the same.

Is it text or actual timestamp datatype? If latter, add four years to it — Sami Kuhmonen, Nov 21 '16 at 07:36
it is actual timestamp, but I resolved with the help of this thread, http://stackoverflow.com/a/4271225/4306705. Thank you — yuri1000, Nov 21 '16 at 07:41

score 2 · Answer 1 · answered Nov 21 '16 at 07:41

2

You can use ADDDATE(), like

UPDATE yourTableName SET del_date = ADDDATE(del_date, INTERVAL 4 YEAR);

answered Nov 21 '16 at 07:41

Sudhir Bastakoti

99,167
15
158
162

1

I used like this, `UPDATE `table` SET `del_date` = REPLACE( `del_date` , '2012', '2016' )` . Is that correct? – yuri1000 Nov 21 '16 at 07:42
1

@yuri1000 yes that can also be the solution – Sudhir Bastakoti Nov 21 '16 at 07:45

MYSql change only year from timestamp

1 Answers1