Jump to content

Date Wrong Format, Wrong Column Type


benanamen

Recommended Posts

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 by benanamen
Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.