如何在MySQL中无需正则表达式统一转换多格式VARCHAR类型日期字符串

Got it, dealing with messy legacy date formats is always a pain, especially when they're all stuffed into a single VARCHAR column. The good news is you can handle this entirely with MySQL's built-in functions without resorting to regex—here's a practical approach tailored to your specific formats:

The Core Idea #

We'll use a CASE WHEN statement to test each date string against the format patterns you've identified. Since STR_TO_DATE() returns NULL when the input doesn't match the pattern, we can chain these checks to find the correct format for each row.

Complete SQL Query #

SELECT 
    datecreated,
    CASE
        -- Match MM-DD-YYYY (e.g., 09-09-1950)
        WHEN STR_TO_DATE(datecreated, '%m-%d-%Y') IS NOT NULL 
            THEN STR_TO_DATE(datecreated, '%m-%d-%Y')
        -- Match M/D/YYYY (e.g., 7/22/1939, 9/17/1981)
        WHEN STR_TO_DATE(datecreated, '%c/%e/%Y') IS NOT NULL 
            THEN STR_TO_DATE(datecreated, '%c/%e/%Y')
        -- Match HH:MM Mmm DD YYYY (e.g., 0:00 sep 26 1975)
        WHEN STR_TO_DATE(datecreated, '%H:%i %b %d %Y') IS NOT NULL 
            THEN STR_TO_DATE(datecreated, '%H:%i %b %d %Y')
        -- Match YYYYMMDD (e.g., 19840109)
        WHEN STR_TO_DATE(datecreated, '%Y%m%d') IS NOT NULL 
            THEN STR_TO_DATE(datecreated, '%Y%m%d')
        -- Match Mmm D YYYY (e.g., May 6 1957)
        WHEN STR_TO_DATE(datecreated, '%b %e %Y') IS NOT NULL 
            THEN STR_TO_DATE(datecreated, '%b %e %Y')
        -- Match HH:MMAM/PM Mmm DD YYYY (e.g., 12:00AM Jun 21 1959)
        WHEN STR_TO_DATE(datecreated, '%h:%i%p %b %d %Y') IS NOT NULL 
            THEN STR_TO_DATE(datecreated, '%h:%i%p %b %d %Y')
        -- Handle time-only values (e.g., 12:00AM) - adjust based on your business rules
        WHEN STR_TO_DATE(datecreated, '%h:%i%p') IS NOT NULL 
            THEN TIMESTAMP(CURDATE(), STR_TO_DATE(datecreated, '%h:%i%p')) -- Use current date + time
        -- Fallback for unrecognized formats
        ELSE NULL
    END AS standardized_date
FROM employee;

Key Details & Adjustments #

• Format Patterns: Each STR_TO_DATE pattern uses MySQL's native format specifiers to match your input formats:
  • %c = numeric month without leading zero, %e = numeric day without leading zero
  • %b = 3-letter month abbreviation (case-insensitive, so sep/Sep/SEP all work)
  • %h = 12-hour format, %p = AM/PM indicator
• Time-Only Values: For entries like 12:00AM, the example uses CURDATE() to append the time to the current date. If your business requires a different default date (e.g., 1900-01-01) or you'd prefer to mark these as invalid (return NULL), just adjust that branch.
• Order of Checks: The order here doesn't cause conflicts because each format is distinct. If you ever add formats that could overlap, prioritize more specific patterns first.
• Date vs Datetime: If you only need a DATE type (no time component), wrap each STR_TO_DATE call in DATE(), e.g., DATE(STR_TO_DATE(datecreated, '%m-%d-%Y')).

Testing #

Run this query against a sample of your data to verify all formats convert correctly. If you find any unhandled cases, just add another WHEN branch with the matching pattern.

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