CODEWITHSUNDEEP
phpmysqldatetimedate
Erocanti
Erocanti

Reputation: 183

Fixing old MySQL date field mistake

I've have a date field in my table that stores dates from a form in a Weekday, Day Month, Year string.

Example: Wednesday, 02, November 2010

the field is also a varchar.

can I loop through the entire table with a php script to convert the dates to a mysql date format or perform this with an SQL query?

I'm not sure that I can preform some certain statistical reports that involve picking out certain dates and date ranges in the format I have now. What are my options?

Upvotes: 1

Views: 123

Answers (2)

Marc B
Marc B

Reputation: 360762

No need to get PHP involved. It can be done directly in MySQL:

ALTER TABLE yourtable ADD fixeddate date;
UPDATE yourtable SET fixeddate=STR_TO_DATE(bad_date_field, '%W, %d, %M %Y');

relevant docs here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Upvotes: 2

Sam Williams
Sam Williams

Reputation: 735

PHP has a built in class called DateTime. Here is a snippet of code that should get you started.

$date = DateTime::createFromFormat('l, d, F Y', 'Wednesday, 02, November 2010');
echo $date->format('Y-m-d');

You can find more info about this class at http://au.php.net/manual/en/book.datetime.php

Upvotes: 1

Related Questions