Convert different date types to a single format-CodePudding

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

CodePudding user response：

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.