Solved

MYSQL select to find bad varchar dates

Posted on 2015-02-04
5
339 Views
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
0
Comment
Question by:doc_jay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 9

Accepted Solution

by:
shivkasi earned 500 total points
ID: 40590342
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
 
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.
0
 

Author Comment

by:doc_jay
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'!
0
 
LVL 34

Expert Comment

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

Author Closing Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
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. . .
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

728 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