Type conversions and Nulls

  • Thanks Tom. After a number of controversial questions and explanations it is great to have one that makes you think and is right on target.

    +1

    Not all gray hairs are Dinosaurs!

  • Please try:

    declare @k int;

    select

    case when @k=0 OR NOT (@k = 0)

    then cast(0 as date)

    else cast('2012-04-01T10:00:00' as datetime)

    end as [when] ;

    and

    declare @k int;

    select

    case when @k=0 OR NOT (@k = 0)

    then cast('04/01/2012T10:00:00' as date)

    else cast('2012-04-01T10:00:00' as datetime)

    end as [when] ;

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • Great question, Tom

    I almost wish you had thrown in an option of '2012-04-01 10:00:00.000' to spice up the thinking a bit more. Of course, then I probably would have gotten it wrong....

    Rob Schripsema
    Propack, Inc.

  • chgn01 (2/5/2013)


    Please try:

    declare @k int;

    select

    case when @k=0 OR NOT (@k = 0)

    then cast(0 as date)

    else cast('2012-04-01T10:00:00' as datetime)

    end as [when] ;

    Well, that will fail with an error at cast(0 as date)

    and

    declare @k int;

    select

    case when @k=0 OR NOT (@k = 0)

    then cast('04/01/2012T10:00:00' as date)

    else cast('2012-04-01T10:00:00' as datetime)

    end as [when] ;

    Well, it seems quite reasonable that cast('2012-04-01T10:00:00' as datetime) should deliver

    2012-04-01 10:00:00.000

    Tom

  • Nice question and explanation. Thanks Tom.

  • Well, the good news is, I got NULLs down pat. The less good news is, I didn't even stop to think about type precedence.

    Conclusion - time for more coffee.

  • Olga B (2/5/2013)


    Well, the good news is, I got NULLs down pat. The less good news is, I didn't even stop to think about type precedence.

    Conclusion - time for more coffee.

    +1 on that.

    And as for the collation specification, I figured that was just Tom's QOD signature since most of his contributions that I recall had to do with that.

    Overall, very good one.

  • Curse you Precedence!

    always the one that I forget about, ah well 🙂

  • Easy one for me .

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • Good question, thank you for writing it!



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Excellent question, Tom, and even better explanation.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Nice question - thanks, Tom!

  • Thanks for the question!

    Best Regards,

    Chris Büttner

  • Rob Schripsema (2/5/2013)


    Great question, Tom

    I almost wish you had thrown in an option of '2012-04-01 10:00:00.000' to spice up the thinking a bit more. Of course, then I probably would have gotten it wrong....

    +1 to that

    Nice question.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Wow, my first really wrong answer. I knew about CASE's type inferral, just forgot. Thanks for the exceptionally good question.

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply