MYSQL select to find bad varchar dates
Posted on 2015-02-04
MYSQL v 5.6.22
I need some help with finding some bad dates in a column 'pat_birthdate' that has a data type of varchar. This wasn't my decision to store these this way, but now I need to fix it. First I want to correct any bad 'birth dates', even ones that are 'NULL', look them up to find the correct value in a different system, update them and then I will convert the column. The dates should all be stored as 'yyyymmdd' but I'm sure there are some that not a complete date because of typos.
Can somebody help me with a select statement to find all dates that do not match 'yyyymmdd'? Maybe a regex expression, which I have no idea to write. What would be the best way to go about finding these with a select statement first?