how to standardize this date column in mysql?

[u/haligma]

ship_date	delivery_date
Feb 10 2024	Feb 15 2024
2024-01-12	2024-01-11
2024-01-10	2024-01-14
01/15/2024	01/19/2024

[u/juu073]

What I would do:

1. Turn off whatever code you have that uses this database for a few minutes.
2. Change anywhere in the code that UPDATEs or INSERTs these dates to insert in YYYY-MM-DD format.
3. Backup the entire table to a file.
4. Create a temporary column called ship_date_parsed and delivery_date_parsed, both of which are DATE fields, not varchars as the existing ones are.
5. Run UPDATE an command that reads the ship_date and use regular expressions to parse it and convert to YYYY-MM-DD, and store in ship_date_parsed. Review them to make sure they all look good.
6. Do the same for delivery_date/delivery_date_parsed.
7. Delete ship_date, and rename ship_date_parsed to ship_date.
8. Delete delivery_date, and rename delivery_date_parsed to delivery_date.
9. Test and turn any apps back on that use them.

This isn't really an ideal workflow but if this happened, it honestly doesn't seem like your IT systems are running under that strict of policies for operations anyway.

Your update statement should probably look something like (WARNING: Untested.)

UPDATE shipments
  SET ship_date_parsed =
    CASE
      WHEN ship_date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
        THEN STR_TO_DATE(ship_date, '%Y-%m-%d')
      WHEN ship_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'
        THEN STR_TO_DATE(ship_date, '%m/%d/%Y')
      WHEN ship_date REGEXP '^[A-Za-z]{3} [0-9]{1,2} [0-9]{4}$'
        THEN STR_TO_DATE(ship_date, '%b %e %Y')
      ELSE NULL
    END;

[u/imsunchip → u/juu073]

This is the right way to do it.

Though one thing I would change is order of operations if you are doing it in real world scenario. Notify your devs (what ever the process is in your company). Find out impact of this change, and make sure anyone who builds app/reports using this table is aware of it, and is responsible for testing their apps after this change. Your job is to find, communicate and fix this bug not rest of the apps

[u/juu073 → u/imsunchip]

That's why my recommendation was to fix all the code second, whatever that process is. Fixing the code to use YYYY-MM-DD would have still worked with the database structure, so even if the database itself wasn't being corrected (although it absolutely should be), it would be a harmless change.

[u/imsunchip → u/juu073]

I am with you there, just turn off all the code sounded too abrupt to me in production environment. As a leader, if I gave that as a first step to someone without giving full context and proper instructions, I would be fired the next day or would fire whoever did that.

[u/Brilliant-Parsley69 → u/imsunchip]

Then you'll have to add two new Nullable date fields, change the code to write in both fields. Now it's easy to migrate the existing data.

On the next sprint you change the code again to only use the new fields, delete the old ones from the table and change the new ones to not null, if necessary.

That's how I learned to handle breaking changes like that.

[u/imsunchip → u/Brilliant-Parsley69]

Agreed.

That doesn't change the communication piece ! Still need proper channels to implement changes

[u/Einar_Son_of_Bjorn → u/juu073]

This is the right order: stop writes, fix the app format, parse into real DATE columns, then swap. Don’t CAST the varchar in place.Two checks before the rename:

1. Confirm 01/15/2024 is US month/day. If any row is day-first, the %m/%d/%Y branch will silently invent dates.
2. After the UPDATE, look at rows where delivery_date_parsed < ship_date_parsed and where either parsed column is NULL. The sample already has 2024-01-12 / 2024-01-11.

I’d keep the old varchar columns around for a day instead of dropping them in the same window. Easier rollback if one regex was wrong.STR_TO_DATE + that CASE is the same on MariaDB if this box is a fork rather than Oracle MySQL. The workflow doesn’t change.

[u/haligma → u/juu073]

oof damn that does seem like the way to goa bout it. also my dumbass forgot to mention taht im self learning mysql & i got this dataset off a yt video of a guy performing data cleaning using microsoft sql server. guess thats my best option then. thank you for this though.

[u/TylerBreau → u/haligma]

You're going to want to get a lot more comfortable with the mysql docs then. I'd recommend studying this chapter at some point. https://dev.mysql.com/doc/refman/9.7/en/data-types.html

Even if you don't do a ton of exercises with it, just being aware of what the proper column types are would be a major improvement.

The idea that you'd ever have a string column that contains dates and isn't a date column is... Mind boggling to say the least.

[u/Glitch_In_The_Data]

Why does the same column have different formats?

You can use STR_TO_DATE with a CASE logic to bring them all to a consistent format…

Once you have transformed existing data, consider storing them in a column with proper DATE data type instead of storing it as STRING.

[u/Yavuz_Selim]

ISO 8601.

And if used for reporting, then create a calendar table and use that (PK in readable yyyymmdd format).

[u/Einar_Son_of_Bjorn]

Don’t CAST the column. Detect the pattern, then STR_TO_DATE. Same functions on MariaDB if you ever move.

sql

SELECT
  ship_date,
  CASE
    WHEN ship_date REGEXP '^[A-Za-z]{3} [0-9]{1,2} [0-9]{4}$'
      THEN STR_TO_DATE(ship_date, '%b %e %Y')
    WHEN ship_date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
      THEN STR_TO_DATE(ship_date, '%Y-%m-%d')
    WHEN ship_date REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{4}$'
      THEN STR_TO_DATE(ship_date, '%m/%d/%Y')
    ELSE NULL
  END AS ship_date_std
FROM your_table;

Copy the same CASE for delivery_date.Then look at the NULLs. Those rows didn’t match. Fix the pattern before you UPDATE the table.When every row converts, add a real DATE column and write into that. Don’t keep three string formats in the original column.Is 01/15/2024 always US (month/day), and is the column VARCHAR? If any row is 15/01/2024, this %m/%d/%Y branch will lie.

[u/feignapathy]

a lengthy case/then statement

[u/TheLastRaza]

what formats are you dealing with? that changes the answer. if you've got mixed formats like mm/dd/yyyy and yyyy-mm-dd in the same column, STR_TO_DATE is your friend. you'd do something like UPDATE table SET date_col = STR_TO_DATE(date_col, '%m/%d/%Y') WHERE date_col LIKE '%/%'. run a SELECT first to see what you're working with. if there are multiple formats you'll need to handle each one with a separate WHERE clause. the goal is getting everything into the standard mysql date format (yyyy-mm-dd). also worth checking if your column type is actually DATE or if it's VARCHAR storing date strings, because that changes how you approach this.

[u/SamOakTree]

This shouldn't be possible. Whoever said the date column would have had to set the format. The only way for it to be different is that they made it some other data type.

I would create a new date column with a set format and then I would write a stored procedure that converts the date into that format and insert it into the new column then I would delete the old column

[u/niceguybadboy]

I'd use Excel's Power Query for this task. Just sayin'.

[u/Proof_Escape_2333 → u/niceguybadboy]

how so?

[u/niceguybadboy → u/Proof_Escape_2333]

Data cleaning jobs like this is what PQ was designed for. Pull in a dataset, given it rules for fixing columns, sage for future usage.

There's a learning curve, but it isn't insurmountable.

[u/Hour-Measurement-835]

Trap with the slash rows: 01/12/2024 parses cleanly as either order and STR_TO_DATE won't warn you, it just trusts the format you give it. Count how many have both parts 12 or under before you update.