Mysql bad date (data) in string field how to find/validate?

Question

I have a string field in Mysql (date fields imported from from MSSQL) and I'm using the following to convert the string value and place it in a new (MYSQL) date-time field,

Update Table_name set STATUS_DATE= STR_TO_DATE(substring_index(SSTATUS_DATE," ",1),'%c/%e/%Y'),

somewhere in the table I have bad data and the query stops and reverses out the results SO I don't know how to find the bad data record to "fix" it Ideas? I'd like to just do a simple query to determine which record does not convert? The error message says That CABANA is not a date, duh, I suspect that the substr index is messed up so I get data from the prior or following field, but I don;t know, and I don't know how to query for bad data? ie that which won't pass the conversion?

Answer 1

You could try something like

SELECT ID, SSTATUS_DATE from TABLE_NAME WHERE NOT (substring_index(SSTATUS_DATE," ",1) REGEXP '^[0-9]+/[0-9]+/[0-9]+$');

This would give you all the rows that don't follow the 00/00/00 format.

Answer 2

You could use the following UDF in a WHERE clause in your update statement.

CREATE FUNCTION IsDate (str VARCHAR(256))
    RETURNS INT
BEGIN
    DECLARE result INT;

    IF ( SELECT LENGTH(DATE(str)) IS NULL ) THEN
        SET result = 0;
    ELSE
        SET result = 1;
    END IF;

    RETURN tp;
END