MYSQL select to find bad varchar dates

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?

thank you
doc_jayAsked:
Who is Participating?
 
shivkasiConnect With a Mentor Commented:
hope this would help !!

To find out the bad dates, you can add condition for more possibilities

SELECT

CASE WHEN LEN (date_text_field)  <> 8                                    THEN 'ERROR'
          WHEN SUBSTRING (date_text_field, 1,1)  NOT IN (1,2)  THEN 'ERROR'  ----(assuming year start 1 with or 2 )
         WHEN SUBSTRING (date_text_field, 5,1)  NOT IN (0,1)   THEN 'ERROR'  --month
        WHEN SUBSTRING (date_text_field, 7,1)  NOT IN (0,1,2,3)   THEN 'ERROR'  --day
ELSE 'May be Okay'

END

FROM table
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
- Is this one table? What the name(s) of the table(s)?
- Do you have rights to to run DLL against this table? If yes, thenbest way is a:
      - Rename the column BirthDate to BirthDateOld:
          EXEC sp_rename 'Table1.BirthDate', 'BirthDateOld';
      - Add a new column as BirthDate as with property Date:
         ALTER TABLE Table1 ADD BirthDateOld Date;
      - Run an Update query like Update Table1 Set BirthDate =  BirthDateOld Where IsDate(BirthDateOld);

Now, run Select * From Table1 Where BirthDate Is Null

This will give you all missing dates and the bad dates (bad format couldn't be converted).

You need now supply the missing dates manuually.
0
 
doc_jayAuthor Commented:
Yes - this is one table

table name 'patient'
column 'pat_birthdate'

thanks for the idea of how to update the column.  For now, I'm just trying to find the bad 'birth dates' so that I can look them up in a different system and correct them.  Some are even 'NULL'!
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
EXEC sp_rename 'patient.pat_birthdate', 'pat_birthdateOld';
GO
ALTER TABLE patient ADD pat_birthdate Date;
GO
Update patient Set pat_birthdate =  pat_birthdateOld Where IsDate(pat_birthdateOld);

It will be easier first run the above and then using the query below locate and correct missing or bad data:

Select * From patient Where pat_birthdate Is Null

Note: Although the above code doesn't change the data, but make sure to test it in non-production environment first.
0
 
doc_jayAuthor Commented:
thanks!  this came with most of what I was hoping for!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.