MySQL function str_to_date () returns an error

Question

I keep getting an error when I try to convert the "CreateDate" column of string date values ​​in my Grades table to mySQL date format using str_to_date (). My data column contains dates in m / d / yy format (ex: 1/26/16 or 3/3/16).

I fulfilled this request:

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

mySQL returns 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 request?

Answer 1

The usual strategy for cleaning up such data 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 are sure that everything is correctly transformed:

ALTER TABLE Estimates DROP COLUMN CreatedDateString

DATE , , INDEX, , , :

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

Answer 2

.

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

, 0000-00-00 , .

SET CreatedDate = STR_TO_DATE( IFNULL(case when CreatedDate = '' then null else createddate end,'1901-1-1'), '%c/%e/%y' )

1901-01-01

tadman:

SET CreatedDate = STR_TO_DATE(case when CreatedDate = '' then null else createddate end, '%c/%e/%y' )

1901-01-01, .

Answer 3

NO_ZERO_DATE SQL:

set @old_sql_mode = @@sql_mode; 
set sql_mode = ''; 

:

UPDATE Estimates
SET CreatedDate = NULLIF(str_to_date(CreatedDate, '%c/%e/%y'), FROM_DAYS(0))

SQL:

set sql_mode = @old_sql_mode;

NO_ZERO_DATE STR_TO_DATE 0000-00-00 , FROM_DAYS(0). , NULLIF NULL.

.