-- Add new_date column if it doesn't exist
ALTER TABLE TEMPORARY_IMPORT_TABLE 
ADD COLUMN IF NOT EXISTS new_date DATE NULL;

-- Update dates with dd/mm/yy format
UPDATE TEMPORARY_IMPORT_TABLE 
SET new_date = STR_TO_DATE(
    CONCAT(
        SUBSTRING_INDEX(`Date of Comm.`, '/', 2),
        '/20',
        RIGHT(`Date of Comm.`, 2)
    ),
    '%d/%m/%Y'
)
WHERE `Date of Comm.` REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}$';

-- Update dates with dd.mm.yy format
UPDATE TEMPORARY_IMPORT_TABLE 
SET new_date = STR_TO_DATE(
    CONCAT(
        SUBSTRING_INDEX(REPLACE(`Date of Comm.`, '.', '/'), '/', 2),
        '/20',
        RIGHT(REPLACE(`Date of Comm.`, '.', '/'), 2)
    ),
    '%d/%m/%Y'
)
WHERE `Date of Comm.` REGEXP '^[0-9]{1,2}\\.[0-9]{1,2}\\.[0-9]{2}$'
AND new_date IS NULL;

-- Update dates with dd/mm/yyyy format
UPDATE TEMPORARY_IMPORT_TABLE 
SET new_date = STR_TO_DATE(`Date of Comm.`, '%d/%m/%Y')
WHERE `Date of Comm.` REGEXP '^[0-9]{1,2}/[0-9]{1,2}/[0-9]{4}$'
AND new_date IS NULL;

-- Update dates with dd.mm.yyyy format
UPDATE TEMPORARY_IMPORT_TABLE 
SET new_date = STR_TO_DATE(
    REPLACE(`Date of Comm.`, '.', '/'),
    '%d/%m/%Y'
)
WHERE `Date of Comm.` REGEXP '^[0-9]{1,2}\\.[0-9]{1,2}\\.[0-9]{4}$'
AND new_date IS NULL;
