Cleaning out bad DateTime data in Staging Table



I am trying to remove bad data from a table.  The data is imported from flat files that is often dirty.  It is easy to spot this bad data from the column that has the date field.  The staging table has the following schema.

TableA
ColumnA VARCHAR(255)
ColumnB VARCHAR(255)
TimeStamp VARCHAR(255)

How can I delete all of the bad rows based on data that does not fit / convert properly to the datetime format?

Sample data:

TableA
ColumnA ColumnB TimeStamp
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Bad Data Bad Data Bad Data
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Bad Data Bad Data Is Scattered
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Bad Data Bad Data Throughout
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Bad Data Bad Data This column
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Bad Data Bad Data  
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:48PM
Good Data Good Data Apr 18 2014  5:49PM

April 18th, 2014 2:08pm

What does it return?

SELECT * FROM tbl WHERE ISDATE(TimeStamp)=0

Free Windows Admin Tool Kit Click here and download it now
April 18th, 2014 2:10pm
ISDATE function is your friend.
April 18th, 2014 2:12pm

you can use try_convert() and try_parse() for these. if you try_convert() and the result is NULL, then the data doesn't fit your format.

http://andrewtuerk.wordpress.com/2012/04/02/try_convert-and-try_parse-for-data-conversions/

  • Marked as answer by Wizgang 14 hours 35 minutes ago
Free Windows Admin Tool Kit Click here and download it now
April 18th, 2014 2:58pm

Thank you very much!  The try convert will work perfect!  :)

April 18th, 2014 3:24pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics