Home Forums Article Discussions Article Discussions by Author Discuss content posted by Anthony Kudin coalesce and nullif RE: coalesce and nullif

  • June 23, 2010 at 9:12 am

    #1184020

    Hugo Kornelis (6/23/2010)

    forjonathanwilson (6/23/2010)

    null date can be cast as null string, but not the other way, interesting.:hehe:

    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

    select coalesce(nullif(@c,@c),nullif(@d,@d))

    select coalesce(nullif(@d,@d),nullif(@c,@c))

    where the results are compared null datetime to null string by coalesce, which completes just fine.