CODEWITHSUNDEEP
sql-servert-sqldatetimeoutofrangeexception
Ian
Ian

Reputation: 79

T-SQL Dealing with Rogue data when Converting to Datetime

I'm pulling data out of a CRM into a reporting system. Unfortunately the developers of CRM thought it a good idea to store a date as a string, and then not put validation on the field.

I'm trying to do this MAX(Convert(datetime,Action.DateOfAction, 102)); however, because there is some invalid data I am getting an out of range error.

How do I work around this? I can't just fix the data as clearly we are still going to get some invalid data entered by users occasionally, and I can't fix the application because I don't have the source.

Thanks

Ian

Upvotes: 0

Views: 140

Answers (2)

jean
jean

Reputation: 4350

It depends on your requirements but there are alternatives. Allen give a good one, another can be using try catch.

By a general way I can create a view or computed column and use isdate to give me the date or return a null if it cannot be "parsed".

Warning, setting date format/language/localization ill change isdate evaluation.

Upvotes: 0

AllenG
AllenG

Reputation: 8190

You'll need to determine what to do with your bad data, but it looks like you want IF ISDATE

IF ISDATE(Action.DateOfAction) //do whatever
ELSE //Handle Bad data

Upvotes: 2

Related Questions