I’m having a little trouble with an inherited data from a former employee. The data is having different date formats in the same table (not really date formats, they are strings defined as VARCHAR). The ones I identified are:
09-09-1950 7/22/1939 9/17/1981 0:00 sep 26 1975 19840109 May 6 1957 12:00AM Jun 21 1959 12:00AM
I’m trying to convert all of them but I’m stuck in identifying under MySQL because I don’t want to rely on REGEX.
Something like this:
SELECT STR_TO_DATE(datecreated,'%m/%d/%Y') as date FROM employee
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
I recommend adding a new column using the proper
DATETIME data type.
ALTER TABLE employee ADD COLUMN datecreated2 DATETIME;
Then copy these improper date values into the new column.
UPDATE employee SET datecolumn2 = COALESCE( STR_TO_DATE(datecolumn, '%m-%d-%Y'), STR_TO_DATE(datecolumn, '%m/%d/%Y'), STR_TO_DATE(datecolumn, '%m-%d-%Y %l:%i'), STR_TO_DATE(datecolumn, '%b %d %Y'), STR_TO_DATE(datecolumn, '%Y%m%d'), STR_TO_DATE(datecolumn, '%M %e %Y %r'), STR_TO_DATE(datecolumn, '%b %e %Y %r'));
I think that should handle all the formats you showed above. I suggest reading https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format and familiarize yourself with those format codes.
The nice thing about STR_TO_DATE() is that it returns NULL if it can’t parse its input datetime argument using the format string argument. So you can use it with COALESCE(), which takes multiple arguments and returns the first non-NULL argument.
Once you do this, I expect there might be a few cases where some existing data wasn’t parsed by any of the format strings. In these cases, datecreated2 will still be NULL because COALESCE() returns NULL if all of its arguments are NULL.
Find the stragglers this way:
SELECT datecreated FROM employee WHERE datecreated2 IS NULL;
Then you can try to come up with further format strings to parse the stragglers, or else if there are few enough, just set them yourself manually, one by one.