mysql将字符串转换为表名,将字符串/ varchar转换为MySQL表之间的日期

I have a large PHP project that I've been working on for my own business.

I have a MySQL table, with about 2600 entries, that I'd like to "fix" the dates in. My current table has 3 VARCHAR columns that contain string dates. I'd like to convert these, into a duplicate table, but change them to type DATE on the way.

Once I make the conversion and everything looks good, I will copy the new table to the old database, and only allow a date picker to enter those dates on a going-forward basis.

Most of the existing string dates are on the format mm-dd-yyyy, example: 05-03-2013. Some are mm/dd/yyyy, example: 05/03/2013, and a few are of the MONTH DAY, YEAR format, example: March 26th, 2013.

What I ultimately want is them to be of the yyyy-mm-dd format, example: 2013-05-02.

I think I can do this using PHP, passing each string date through strtotime() and then date() but was wondering if MySQL can do this directly.

解决方案

Have a look at STR_TO_DATE function:

UPDATE yourtable

SET

new_column = STR_TO_DATE(varchar_date, '%m-%d-%Y')

WHERE

STR_TO_DATE(varchar_date, '%m-%d-%Y') IS NOT NULL

And then the same query, with date format as '%m/%d/%Y', and then again with '%M %D, %Y'.

If the format doesn't match, STR_TO_DATE will return NULL and rows won't be updated.

If you wish to insert your existing data to a new table, you could use something like this:

INSERT INTO new_table (ID, col1, col2, new_column)

SELECT

ID, col1, col2,

COALESCE(STR_TO_DATE(varchar_date, '%m-%d-%Y'),

STR_TO_DATE(varchar_date, '%m/%d/%Y'),

STR_TO_DATE(varchar_date, '%M %D, %Y'))

FROM oldtable

(new_column is a date column, and you can then format how you want using PHP or using DATE_FORMAT)