How to standardize a dirty date field in MySQL

user_1082410 0
Viewed 31

I have a MySQL table that contains column representing a date and is stored as a string.

The dates in this column (date) are not standard (dirty) and can range from

"Jan 5, 2004" or "Jun 22 2:45 AM"

For the records that are missing the year I have another column (OpeningDate) that can be null or "22 June 2005" and "Deadline" which is a dirty column with values like ("26 January 2004", "01 July 2005, 6 pm ABOUT: BearingPoint, Inc. Commercial Law and Economic Regulation Program")

How do I go about to get a normalized representation of the values in the date field.

For other tables I've been able to normalize the date field by using the following queries but for this table the solutions I come up with are too convoluted and not even close to accurate.

SELECT DATE_FORMAT(STR_TO_DATE(DATE,'%M %d, %Y'), '%Y-%m-%d') FROM `data job posts`
mysql
1 Answers

I'm not sure there is a clean way to do this since strings are very much non normalized. The cleanest approach would likely be to chunk the data being modified and identify patterns that are identifiable so that you can reduce the size of the dataset to a smaller group of highly unnormalized strings.

As an example something similar to this:

UPDATE table 
   SET DATE = CASE WHEN DATE LIKE '^alnum+, digit+$' THEN DATE_FORMAT(STR_TO_DATE(DATE,'%M %d, %Y'), '%Y-%m-%d') ELSE DATE END,
       DATE = CASE WHEN DATE LIKE '^alnum+:alnum+$' THEN DATE_FORMAT(STR_TO_DATE(DATE,'%M %d %l:%i %p'), '%m-%d') ELSE DATE END;

It might help to create this as a new column and rename the new column when dropping the old one once the operation is complete or creating this as a new table if the current table is live and needs to be queryable as updating records may lock the table.

user_51863440
Related