Date Wrong Format, Wrong Column Type

[benanamen]

On another forum I ran across someone with a problem I have never had to deal with and am interested in the solution for it.

 

He has dates in varchar formatted as 12/25/2015 and wants to update so the date is formatted as a proper date (YYYY-MM-DD) with a date column type.

 

Off the top of my head I was thinking,

 

Create a new date type column

 

Do an update on the dates to replace the slashes with dashes.

 

UPDATE table SET baddate = REPLACE(baddate, '/', '-');

 

Then make a little script to query the bad dates in reverse twice and re-insert the data with a foreach to the correct date column.

 

SELECT reverse(baddate) AS rev,

(SELECT REVERSE(rev) FROM mytable)

FROM mytable

 

foreach ($result AS $row){

//Insert data to real date column

}

 

I am wondering what a 100% sql solution would be or any other ideas to deal with it.

Edited December 18, 2015 by benanamen

[mac_gyver]

STR_TO_DATE() would be used in a single UPDATE query to take any formatted date and produce a mysql date type.

[benanamen]

After some digging I found something that works

UPDATE your_table
SET date_field = DATE(STR_TO_DATE(date_field, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(date_field, '%m/%d/%Y')) <> '0000-00-00';

[Jacques1]

I definitely would not try to fix this with some SQL hack job. A VARCHAR can contain absolutely everything, and nobody knows if the dates have been validated properly, so simply assuming that the original data is alright seems overly optimistic.

 

I'd do this with a script which actually parses the original dates, validates them rigorously and then either inserts the reformatted date into the database or reports the input for manual correction. If it turns out that all dates could be reformatted automatically, that's great. But I wouldn't rely on it.

 

Also note that the reliability of STR_TO_DATE() is dependend on the MySQL configuration. In less strict SQL modes, nonsense dates like “2015-02-31” are considered valid and will be accepted with no warning whatsoever.

Edited December 18, 2015 by Jacques1