mysql to_date()不存在,mySQL str_to_date（）函数返回错误

I keep receiving an error message when trying to convert a column, CreatedDate, of string date values in my Estimates table into the mySQL date format using str_to_date(). My column of data contains dates in m/d/yy format (for example: 1/26/16 or 3/3/16).

I ran this query:

UPDATE Estimates

SET CreatedDate = str_to_date( CreatedDate, '%c/%e/%y' )

mySQL is returning this error message:

Error

SQL query:

UPDATE Estimates

SET CreatedDate = str_to_date( CreatedDate, '%c/%e/%y' )

MySQL said: #1411 - Incorrect datetime value: '' for function str_to_date

What is wrong with my query?

解决方案

The usual strategy for cleaning up data like this is as follows:

ALTER TABLE Estimates CHANGE COLUMN CreatedDate CreatedDateString VARCHAR(255);

ALTER TABLE Estimates ADD COLUMN CreatedDate DATE

UPDATE Estimates SET CreatedDate=STR_TO_DATE(CreatedDateString, '%c/%e/%y'))

WHERE CreatedDateString IS NOT NULL AND CreatedDateString != ''

Then when you're confident everything got converted correctly:

ALTER TABLE Estimates DROP COLUMN CreatedDateString

The advantage to proper DATE fields is they're in a consistent format and when you add an INDEX on them data retrieval is very fast, even on ranges, like:

SELECT * FROM Estimates WHERE CreatedDate BETWEEN '2016-01-01' AND '2016-06-30'