Lately I’ve gotten a lot of questions submitted to SQLServerCentral that have dealt with weird data type issues. We had quite a discussion about one conversion of data types and I think everyone learned a few things.

I wrote an article years ago about CASTing and converting data after I learned a few things solving some issues at one of my jobs. It was a popular article, though I’m sure it needs some updating with the releases of SQL Server 2005 and SQL Server 2008. Some of those conversions might not apply, and there are definitely new ones with XML and other types of data.

A few of the interesting things I’ve been reminded of lately:

- cast( xx as varchar) defaults to varchar(30). An old habit of mine is to never do this. Always specify a size, but if you’re losing data, that might be why.

- select cast( (10 + 10.00) as varchar(50)), it’s a character value, but you ought to know the rules of precedence for implicit conversions. Float trumps int, so the decimals are preserved.

There are lots more, but I think it’s worth periodically reviewing the implicit casting rules as well as the order of precedence for conversions.