concatenatining zero returns unexpected behavior

  • I know how to get around it, but I was curious if anybody know why this is happening.

    select

    case

    when len(datepart(mm, getdate())) = 1 then '0'+convert(char(2), datepart(mm, getdate()))

    else datepart(mm, getdate())

    end

    This currently of course returns a value of 4, but I was expecting it to return 04. After a bit of testing, I find that it seems to be doing an implicit conversion of '0' to int and then a math calculation first before the concatenation.

    If you change the 0 to an alpha, it will error out with a conversion error.

    Interestingly enough, if I change the value 0 to anything else like 1 or 10, etc. It will do the concatenation it correctly.

  • I'm thinking it has to do with the fact that one branch of your case yields a Char(2), and the other, an integer. In order to remain consistent, the char(2) is being implicitly converted BACK to an integer.

    Try this instead:

    select

    case

    when len(datepart(mm, getdate())) = 1 then '0'+convert(char(2), datepart(mm, getdate()))

    else convert(char(2),datepart(mm, getdate()))

    end

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Interesting. I tried using:

    select right(char(48) + datepart(mm, getdate()), 2)

    and got "4", instead of "04". Would have thought that would do it, but was wrong.

    On the other hand:

    select right(char(48) + cast(datepart(mm, getdate()) as varchar(2)), 2)

    worked. "04".

    So did:

    select right('0' + cast(datepart(mm, getdate()) as varchar(2)), 2)

    and

    select right(cast(0 as varchar(2)) + cast(datepart(mm, getdate()) as varchar(2)), 2)

    I use the Right() string function instead of a case statement for these. It works no matter how many leading zeroes you want to deal with.

    As for this:

    select

    case

    when len(datepart(mm, getdate())) = 1

    then cast(0 as varchar(2))+convert(varchar(2), datepart(mm, getdate()))

    else datepart(mm, getdate())

    end

    Even with both conversions in it, still doesn't work.

    I'm curious how you worked around it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Check out BOL for CASE and notice the following:

    ELSE else_result_expression

    Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

    To make your statement work as intended, adjust the ELSE expression, as below:

    select

    case

    when len(datepart(mm, getdate())) = 1 then '0'+convert(char(2), datepart(mm, getdate()))

    else convert(char(2),datepart(mm, getdate()))

    end

    However, you can accomplish the same with one line of code:

    select convert(char(2),getdate(), 1)

    GL!

  • Yep, Matt's right (as usual). There's an implicit conversion because of the "else" statement.

    I tried this, to see what SQL would do:

    select

    case

    when len(datepart(mm, getdate())) = 1

    then cast(0 as varchar(2))+convert(varchar(2), datepart(mm, getdate()))

    when 1 = 1 then 'aa'

    else datepart(mm, getdate())

    end

    And got "4", no leading zero. I wonder what that will do in October?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Gsquared - you're still only converting one branch of the CASE statement.

    As in -

    select

    case

    when len(datepart(mm, getdate())) = 1

    then cast(0 as varchar(2))+convert(varchar(2), datepart(mm, getdate())) --<--this is Char(2)

    else datepart(mm, getdate()) --<--This is integer

    end

    When you have a case like this, you end up in one of those cases where SQL needs to try to figure out which of the two types to return. In this case - it chooses INT (according to the "data type precedence" rules)....

    Data type precedence rules described here....(note that higher precedence =lower number in the article)

    http://msdn2.microsoft.com/en-us/library/ms190309.aspx

    Thanks to that - you have to CONVERT the ELSE statement too, or else you keep getting the INT.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • SELECT CONVERT(CHAR(2), GETDATE(), 103)


    N 56°04'39.16"
    E 12°55'05.25"

  • As for your original question:

    It depends on which line of CASE statement get evaluated first.

    Since the two lines returns different datatypes (line 1 varchar, line 2 int) the first line to return a result decides which datatype entire resultset will have.


    N 56°04'39.16"
    E 12°55'05.25"

  • Peso (4/16/2008)


    SELECT CONVERT(CHAR(2), GETDATE(), 103)

    Format 103 has the Day first. You need to use formats 1, 101, 10 or 110 to get a 2 digit month as the first 2 characters.

  • Matt, I think we cross-posted. See my prior cross-post to your cross-post of my first post. 🙂

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Peso (4/16/2008)


    As for your original question:

    It depends on which line of CASE statement get evaluated first.

    Since the two lines returns different datatypes (line 1 varchar, line 2 int) the first line to return a result decides which datatype entire resultset will have.

    Sorry, but I don't think this is quite correct. Run the following example.

    select

    case

    when len(datepart(mm, getdate())) = 1 then '05'+convert(char(2), datepart(mm, getdate()))

    else convert(char(2),datepart(mm, getdate()))

    end

    select

    case

    when len(datepart(mm, getdate())) = 1 then '05'+convert(char(2), datepart(mm, getdate()))

    else datepart(mm, getdate())

    end

    Notice that the first statement returns "054", because the ELSE expression is also CHAR. The second statement returns "54", indicating that the same expression is still evaluating to true, yet the result is converted to INT to match the ELSE expression.

    Take a look at both my previous post with the BOL excerpt and Matt's with the link to data type precedence.

  • GSquared (4/16/2008)


    Matt, I think we cross-posted. See my prior cross-post to your cross-post of my first post. 🙂

    yup! Looks like the issue is well in hand...It's one of those fun "whoa - this thread just blew up!" things...:D

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • John Beggs (4/16/2008)


    Peso (4/16/2008)


    As for your original question:

    It depends on which line of CASE statement get evaluated first.

    Since the two lines returns different datatypes (line 1 varchar, line 2 int) the first line to return a result decides which datatype entire resultset will have.

    Hey wow. Thanks guys, this was driving me nuts a bit. Go figure, I never knew that about a case statement.

    I knew there was a data precedence rule, but I didn't know how that was affecting it due to the else portion. I should have figure it still part of the case expression.

    As to the poster who asked how I worked around it... Please don't laugh...

    declare @Month varchar(2)

    set @Month = datepart(mm, getdate())

    select case

    when len(@Month) = 1 then '0'+@Month

    else @Month

    end

    No implicit conversion here! 🙂

    I don't like

    SELECT CONVERT(CHAR(2), GETDATE(), 103)

    This only works if the you have a true datetime datatype. If you switch getdate() with say '2008/01/15', it would return 20.

  • Matt Miller (4/16/2008)


    GSquared (4/16/2008)


    Matt, I think we cross-posted. See my prior cross-post to your cross-post of my first post. 🙂

    yup! Looks like the issue is well in hand...It's one of those fun "whoa - this thread just blew up!" things...:D

    Actually, my standards for "exploding threads" have been changed by the ones where the Question of the Day is wrong. Like http://www.sqlservercentral.com/Forums/Topic464861-1181-18.aspx 🙂

    This thread? Not so exploded.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 14 posts - 1 through 14 (of 14 total)

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