T-SQL Dealing with Rogue data when Converting to Datetime

Question

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

This may hold your answer: http://stackoverflow.com/questions/2000045/tsql-cast-string-to-integer-or-return-default-value — Gavin, Nov 29 '13 at 18:41
Have you read anything about artificial intelligence? Maybe it can figure out what the user intended... but I cannot. Datetimes can cast to floats. If you cannot cast it to a float, it's not a valid datetime. Can you help us understand what you'd like to happen? If it cannot cast, null? — SQLMason, Nov 29 '13 at 18:43

score 2 · Answer 1 · answered Nov 29 '13 at 18:45

2

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

answered Nov 29 '13 at 18:45

AllenG

8,112
29
40

score 0 · Answer 2 · answered Nov 29 '13 at 19:23

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.

T-SQL Dealing with Rogue data when Converting to Datetime

2 Answers2