Solved

MYSQL select to find bad varchar dates

Posted on 2015-02-04
5
335 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
  • 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 33

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 33

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

776 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