Hugo Kornelis (6/23/2010) forjonathanwilson (6/23/2010)
null date can be cast as null string, but not the other way, interesting.
This is not correct. A null string can be cast as date (result will be NULL). The problem starts when you try to case a non-null string as date - in that case, the string must evaluate to a valid date. And obviously, the string 'Test' does not qualify there.
thanks for clarifying that for me. I also checked this myself after I made my irroneous assumption:
declare @c varchar(10), @c1 varchar(10),
@d datetime, @d1 datetime
select @c = 'test', @c1 = null, @d = '1/1/10', @d1 = null
where the results are compared null datetime to null string by coalesce, which completes just fine.