特定ID的日期格式统一修改方案求助（VARCHAR列处理）

Got it, let's work through this date format issue step by step. The problem with your current approach is that STR_TO_DATE('%d-%m-%Y') only handles one of your two date formats—so it’ll either fail or mangle the yyyy-mm-dd hh:ii entries. Here’s a reliable way to standardize everything:

Step 1: Validate Conversion Logic First #

Before making any changes to your table, run a test query to confirm the conversion works for both formats:

SELECT 
  `dateColumn` AS original_date,
  CASE
    -- Handle yyyy-mm-dd hh:ii format
    WHEN STR_TO_DATE(`dateColumn`, '%Y-%m-%d %H:%i') IS NOT NULL THEN
      DATE_FORMAT(STR_TO_DATE(`dateColumn`, '%Y-%m-%d %H:%i'), '%d-%m-%Y %H:%i')
    -- Keep existing dd-mm-yyyy hh:ii format as-is
    WHEN STR_TO_DATE(`dateColumn`, '%d-%m-%Y %H:%i') IS NOT NULL THEN
      `dateColumn`
    -- Catch invalid date strings (adjust this if needed)
    ELSE NULL
  END AS standardized_date
FROM metric;

This uses STR_TO_DATE’s behavior where it returns NULL if the input string doesn’t match the specified format. The CASE statement routes each entry to the correct conversion logic.

Step 2: Update the Table #

Once you verify the test results look correct, run the update to standardize all dates:

-- Standardize all date strings to dd-mm-yyyy hh:ii format
UPDATE metric
SET `dateColumn` = CASE
    WHEN STR_TO_DATE(`dateColumn`, '%Y-%m-%d %H:%i') IS NOT NULL THEN
      DATE_FORMAT(STR_TO_DATE(`dateColumn`, '%Y-%m-%d %H:%i'), '%d-%m-%Y %H:%i')
    WHEN STR_TO_DATE(`dateColumn`, '%d-%m-%Y %H:%i') IS NOT NULL THEN
      `dateColumn`
    ELSE `dateColumn` -- Keep invalid entries, or set to NULL if preferred
  END;

If you actually wanted dd-mm-yy hh:ii (2-digit year) instead of full 4-digit year, just replace %Y with %y in the DATE_FORMAT string.

Step 3: Alter the Column to DATETIME #

Now that all strings are valid dates in a consistent format, you can safely convert the column to DATETIME type:

ALTER TABLE metric MODIFY `dateColumn` DATETIME;

Important Notes #

• Backup first: Always take a backup of your table before running updates or schema changes.
• Clean invalid entries: If you have any strings that don’t match either date format, the ELSE clause will leave them as-is—but these will cause the ALTER TABLE step to fail. You’ll need to either fix those entries or set them to NULL before converting the column type.
• Time precision: If your times have seconds (you mentioned hh:ii, but just in case), adjust the format strings to include :%s where needed (e.g., %Y-%m-%d %H:%i:%s).

内容的提问来源于stack exchange，提问作者Oualid Oukassou