CODEWITHSUNDEEP
phpmysqlphpmyadmin
user191688
user191688

Reputation: 2659

changing date fields with incorrect year using sql in phpMyAdmin

I have a live db that has some 2011 dates that were entered as only mm/dd and these defaulted to 2012 dates. (eg. 2011-03-16 was entered as 03-16 and stored as 2012-03-16)

As a result, the db has some transaction dates in the future. I have fixed the code to correct the input, but I need to fix the incorrect dates in the db.

I can manually edit these with phpMyAdmin but that would take hours.

The ideal solution would be SQL to correct these dates in the SQL window in phpMyAdmin.

The selection would be:

SELECT created FROM posts WHERE created>'2012-02-01'

and then subtract 1 year from each result and store it.

To phrase it another way, I need to take each created field and if it is a future date subtract 1 year to make it a 2011 date, and then save it.

Upvotes: 1

Views: 608

Answers (3)

Fosco
Fosco

Reputation: 38506

Should be pretty simple using the DATE_ADD() function.

update posts set created = DATE_ADD(created, INTERVAL -1 YEAR) where created > '2012-02-01'

source: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

edit: oops, updated syntax.

Upvotes: 1

chris
chris

Reputation: 565

UPDATE testdate
SET DATUM = DATE_ADD(datum, INTERVAL -1 YEAR) 
WHERE DATUM > NOW();

Upvotes: 0

dfsq
dfsq

Reputation: 193261

UPDATE posts SET created = DATE_ADD(created, INTERVAL -1 YEAR) 
WHERE created > CURDATE()

Upvotes: 1

Related Questions