Cliff Jones (8/19/2009)
If you rely on implicit conversion, is it going to convert the CHAR to DATETIME or DATETIME to CHAR? You are never really sure so you explicitly convert the side of the equation that has the least impact.
You're not sure if you haven't memorised the precedence order of data types. :-)
If you suspect colleagues haven't memorised them, you do explicit CAST or CONVERT (I think CAST is ANSI SQL)...
In an expression, including an equality or inequality test, one data type is elevated to match the other.
So IF ( 'abc' < N'def' ) is tested in Unicode characters - which usually doesn't make a difference. Collation may, but don't look at me there :-) I hope datetime ranks above char because I've been using the likes of
" WHERE (date > '2009-04-15') " for ages. But there isn't one char expression of a date, anyway (I just said). The one you're mainly liable to try that won't work is such as
'A' + 1, where presumably you want 'A1'.
There I use STR(), sometimes REPLACE(STR(...), ' ', '0').
In an assignment (SET x = y), of course conversion is from the type of expression y to the type of column/variable/whatever, x.