Data Conversion Woes, pt 2

In Part 1 I discussed why some queries that look like they should work fail with a conversion error. In this part, I’m going to go over some solutions to the problem.

1. Probably best solution, at least in the long term is, as I mentioned previously, to correct the data type of the column and to remove the data that doesn’t fit. This is probably, however, the hardest of the solutions to implement, due to the potential impact.

1. Don’t do the conversion. If you can get away without converting the data types, then do so. In my example, because the date was in the format yyyy/mm/dd, a string comparison produces the expected results.

   SELECT * FROM tbl_TestingConversion
    WHERE ISDate(TheDate)=1
    AND TheDate > '2007/07/01'

2. Use a case statement in the WHERE clause. Turn the non-date values to NULL and then compare. The downside is that SQL will be completely unable to use an index if one exists on the compared column.

   SELECT *
     from tbl_TestingConversion
     where CAST(CASE ISDate(TheDate) WHEN 1 THEN TheDate ELSE Null END AS DATETIME) > '2007/07/01'

That’s a few ways to work around conversion errors. Anyone else have a preferred method that they’d like to share?