-- Standardize date format
UPDATE TEMPORARY_IMPORT_TABLE 
SET new_date = 
    CASE 
        -- Handle dd/mm/yy format
        WHEN `Date of Comm.` REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}$' 
        THEN STR_TO_DATE(
            CONCAT(
                SUBSTRING_INDEX(`Date of Comm.`, '/', 2), 
                '/20', 
                SUBSTRING_INDEX(`Date of Comm.`, '/', -1)
            ), 
            '%d/%m/%Y'
        )
        -- Handle dd.mm.yy format
        WHEN `Date of Comm.` REGEXP '^[0-9]{1,2}\\.[0-9]{1,2}\\.[0-9]{2}$' 
        THEN STR_TO_DATE(
            CONCAT(
                REPLACE(SUBSTRING_INDEX(`Date of Comm.`, '.', 2), '.', '/'),
                '/20',
                SUBSTRING_INDEX(`Date of Comm.`, '.', -1)
            ),
            '%d/%m/%Y'
        )
        -- Handle dd/mm/yyyy format
        WHEN `Date of Comm.` REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$' 
        THEN STR_TO_DATE(`Date of Comm.`, '%d/%m/%Y')
        -- Handle dd.mm.yyyy format
        WHEN `Date of Comm.` REGEXP '^[0-9]{1,2}\\.[0-9]{1,2}\\.[0-9]{4}$' 
        THEN STR_TO_DATE(REPLACE(`Date of Comm.`, '.', '/'), '%d/%m/%Y')
        ELSE NULL
    END;
