What will convert return?

  • Bajrang

    SSCrazy

    Points: 2074

    Comments posted to this topic are about the item What will convert return?

    -RP
  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Good one. Actually whenever we try to convert any number data type (int, float, money, decimal,...) in Character format (char, varchar, nchar, nvarchar) and Character format length is less then number format, then it always throw error or incorrect result (*). It's a thumb rule (for me)

    πŸ™‚

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Good question, oldie but a goodie.

    Explanation however is incorrect. The problem is not that the value exceeds the upper limit of an integer , I'm pretty sure 5555555 can fit in an integer, but the problem is that the string 5555555 is 7 characters long and you're trying to convert it to 6 characters. This will lead to a truncation, which for some data types will give * when using the CONVERT function.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Kingston Dhasian

    SSCoach

    Points: 19794

    The explanation is not completely correct

    The upper limit for INT data type is 2^31 - 1( 2,147,483,647 ), the number 5555555 does not exceed this limit

    As Danny has said above, the issue is because VARCHAR(6) is not capable of accepting the 7 digit number

    Change it VARCHAR(7) and you will get a proper result


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • psingla

    Hall of Fame

    Points: 3840

    Good question..and I have seen this behavior earlier as well.

    As per BOL, "*" is returned when we try to convert to char and varchar and

    Error message when try on convert to nchar, nvarchar...

    Any specific reason why Microsoft has implemented this way?

    I would have implemented error in all cases either it be char,varchar,nvarchar or nchar to keep it simple and easy to developer πŸ˜‰

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • paul s-306273

    SSChampion

    Points: 10615

    How about BOL for the explanation - that just says you get '*'.

  • Toreador

    SSChampion

    Points: 11261

    Kingston Dhasian (7/11/2013)


    The explanation is not completely correct

    Worse than that, the explanation is completely incorrect πŸ™

    It gives two "facts" (that this number exceeds the upper limit of an integer, and that exceeding the upper limit for an integer would cause this behaviour), boith of these "facts" are false.

    The number does not exceed the maximum for an integer. And if it did, it would lead to an error rather than '*'.

    The '*' is because the converted number is longer than the 6 characters into which it is being put.

  • udayroy15

    Ten Centuries

    Points: 1076

    Good question πŸ™‚

  • Jeff Moden

    SSC Guru

    Points: 997127

    Even though the explanation of exceeding the max INT value is completely wrong, I'm absolutely amazed that more than 1/3 of the people that answered this question got it wrong.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mtassin

    SSC-Insane

    Points: 23099

    Koen Verbeeck (7/11/2013)


    Good question, oldie but a goodie.

    Explanation however is incorrect. The problem is not that the value exceeds the upper limit of an integer , I'm pretty sure 5555555 can fit in an integer, but the problem is that the string 5555555 is 7 characters long and you're trying to convert it to 6 characters. This will lead to a truncation, which for some data types will give * when using the CONVERT function.

    Yah... I guess I should be up earlier than Koen if I ever want a chance to explain something I understand. πŸ™‚

    This is it exactly... after all int in SQL is 2.1 billion as the max value... 5.5 million easily fits there, just doesn't fit into a 6 character string. πŸ™‚



    --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]

  • TomThomson

    SSC Guru

    Points: 104773

    Jeff Moden (7/11/2013)


    Even though the explanation of exceeding the max INT value is completely wrong, I'm absolutely amazed that more than 1/3 of the people that answered this question got it wrong.

    Well, more than half of those who got it wrong simply counted wrong. Quite apart from the wrong (in two ways) explanation, the use of "5555555" when the number of digits mattered with "555555" as one of the answer options is just an exercise in counting characters. Maybe the 23% who (to date) picked that particular wrong answer could have got it right if instead of "5555555" and "555555" the question had contained '1234567' and the answer '123456'. Of course by now, with all the questions requiring absolute accuracy in reading and comparing, rather than knowledge of SQL Server, it's maybe a bit surprising that almost a quarter of people fell for that trick. Perhaps after all they thought that the conversion would silently truncate, in which case I would have to share your amazement.

    Tom

  • Jedak

    SSCertifiable

    Points: 5087

    L' Eomot InversΓ© (7/11/2013)


    Jeff Moden (7/11/2013)


    Even though the explanation of exceeding the max INT value is completely wrong, I'm absolutely amazed that more than 1/3 of the people that answered this question got it wrong.

    Well, more than half of those who got it wrong simply counted wrong. Quite apart from the wrong (in two ways) explanation, the use of "5555555" when the number of digits mattered with "555555" as one of the answer options is just an exercise in counting characters. Maybe the 23% who (to date) picked that particular wrong answer could have got it right if instead of "5555555" and "555555" the question had contained '1234567' and the answer '123456'. Of course by now, with all the questions requiring absolute accuracy in reading and comparing, rather than knowledge of SQL Server, it's maybe a bit surprising that almost a quarter of people fell for that trick. Perhaps after all they thought that the conversion would silently truncate, in which case I would have to share your amazement.

    Another good explanation would be that they might expect it to function the way the the string conversion works. Which is to truncate the string to specified length.

    DECLARE @sTest varchar(11)

    SET @sTest = '12345678901'

    SELECT convert(varchar(10),@sTest)

    --Shows 1234567890

  • Bajrang

    SSCrazy

    Points: 2074

    Thank you SSC for posting this question.. πŸ™‚

    and

    Thank you all members.. Yes, explanation provided on BOL link doesn't fit but couldn't find any other source to support this. πŸ˜€

    -RP
  • Kingston Dhasian

    SSCoach

    Points: 19794

    Bajrang (7/11/2013)


    Thank you SSC for posting this question.. πŸ™‚

    and

    Thank you all members.. Yes, explanation provided on BOL link doesn't fit but couldn't find any other source to support this. πŸ˜€

    The explanation provided in BOL is correct. Check the block Truncating and Rounding Results in the link you have provided.

    Correct answer: *

    Explanation:

    Since this value exceeds the upper limit of an integer, a * is returned.

    Ref: http://msdn.microsoft.com/en-us/library/ms187928.aspx%5B/quote%5D

    The explanation that you have provided above is actually incorrect because of the following reasons

    1. The value "5555555" does not exceed the limit for INT data type

    2. An asterisk(*) is returned because you are trying to convert a 7 digit integer to VARCHAR(6).

    If you try to convert it to VARCHAR(7) or greater it will give you the expected answer.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Bajrang

    SSCrazy

    Points: 2074

    The explanation provided in BOL is the link I have provided!! Are you saying, one is correct but not the other!!!

    " * = Result length too short to display. E = Error returned because result length is too short to display." in section [Truncating and Rounding Results]

    -RP

Viewing 15 posts - 1 through 15 (of 30 total)

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