I updated a database column today to utilize the SQL 2008 DATE type, upgrading from a SMALLDATETIME which always had a 00:00:00 hh:mm:ss component. I was fairly certain that this would not break any existing code, so I did not perform an exhaustive code search. My mistake.
A function broke with the following message:
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int
"Strange," I thought. "How would an int be getting used as a date?"
Of course there's the basic method of datetime construction from a string: CAST("2012-01-01" as datetime), but that doesn't involve any INTs. Did someone try to use a similar INT (20120101) in place of the string? No, because that would cause an arithmetic overflow...
Well, it turns out that there's another way of using INTs as DATETIMEs:
select cast(CAST('2012-01-10' as datetime) as int)
Apparently this doesn't work any more:
select cast(CAST('2012-01-10' as date) as int)
And this is what was being performed in the function in question:
CASE WHEN io_end > 40947 THEN 1 ELSE 0 END
Which promptly broke when I changed the column to a DATE. So let this be a warning: even something as innocuous as a DATETIME to DATE change needs QA and code search.