tsb5270
asked on
Converting invalid dates to Nulls
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Unfortunately, isDate does not work because it interprets 01/01/0985 as a valid date. Sql Server 2005 apparently is much pickier and rejects that date because it is not in the range of 1/1/1753 and 12/31/9999.
Instead of using isDate, during the process where I convert the non-sql data to textfiles I'm just checking to see if it's in the range. If not, I set the value to null. Both responses above helped me arrive at this solution, so I'm splitting the points.
Instead of using isDate, during the process where I convert the non-sql data to textfiles I'm just checking to see if it's in the range. If not, I set the value to null. Both responses above helped me arrive at this solution, so I'm splitting the points.
ASKER