Although it’s rotten practice, values like dates, times, numbers, etc are often stored in columns with the wrong data type. Sometimes, it’s because the data model needs to allow for multiple types of data in the same column (sometimes even on the same row in the form of a formula or text syntax), but sometimes, it’s just plain and simple lazy.
With this type of setup, it’s only a matter of time before you run into conversion errors like this one:
Msg 245, Level 16, State 1, Line 29 Conversion failed when converting the varchar value 'xyz' to data type int.
To make things worse, these errors can be tricky to pinpoint, because they can appear to come and go without any real recognizable pattern. In this post, I’ll take a look at how this happens, how to look for these errors, and ultimately how to fix them if you can’t change the database schema.
sqlsunday.com 