troubleshooting Question

Converting invalid dates to Nulls

Avatar of tsb5270
tsb5270 asked on
Microsoft SQL Server
4 Comments2 Solutions617 ViewsLast Modified:
I have a situation where I need to bring data from an external non-SQL database into a SQL Server 2005 database.  Our process exports the non-SQL database records into textfiles, and then we set up an SSIS package to import the data into SQL Server.  The problem I am running into is that dates were are validated at the time data is entered into the old database.  So there are a number of records that contain dates that look something like this: 08/29/0985.  Anyway, our SSIS fails because these are not real dates.  I tried setting the SSIS to ignore failures for the date fields, but it fails anyway.  So I think my options are to try to catch the bad dates when I'm exporting to the text file, or to import the date fields into SQL Server as varchars and then modify my any SQL statements where date comparisons are needed to simply interpret the invalid dates as nulls.  There me be other options I'm not aware of.  So I guess I'm asking if there is a way to "trap" an invalid date conversion inside a sql statement.  

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros