MYSQL select to find bad varchar dates

Posted on 2015-02-04
Medium Priority
Last Modified: 2015-02-08
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
Question by:doc_jay
  • 2
  • 2

Accepted Solution

shivkasi earned 2000 total points
ID: 40590342
hope this would help !!

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


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'


FROM table
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40590349
- 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.

Author Comment

ID: 40590353
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'!
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40590367
EXEC sp_rename 'patient.pat_birthdate', 'pat_birthdateOld';
ALTER TABLE patient ADD pat_birthdate Date;
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.

Author Closing Comment

ID: 40597101
thanks!  this came with most of what I was hoping for!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question