Solved

MYSQL select to find bad varchar dates

Posted on 2015-02-04
5
336 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 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

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …

856 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