Casting question

  • Eli Leiba

    SSC-Addicted

    Points: 461

    Comments posted to this topic are about the item Casting question

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question - had to think long and hard for the correct answer

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • demonfox

    SSCertifiable

    Points: 6289

    cheeky ...

    didn't occur a doubt on the 0 😛

    though I had a doubt over

    select isnull ( convert (char(3),1.0/9) ,'*')

    -- or

    select isnull ( convert (char(3),1/9.0) ,'*')

    and it clears out as it does throws an error

    Msg 8115, Level 16, State 5, Line 1

    Arithmetic overflow error converting numeric to data type varchar.

    When Implicit conversion occurs , it gives out the output with default precision of 18,6..

    so 1.0/9 is 0.111111..

    Select select isnull ( convert (char(8),1.0/9) ,'*')

    anything less than 8 would have the arithmetic overflow error for this scenario 😀

    Nice question , though ...

    Thanks for the question ..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Dineshbabu

    Hall of Fame

    Points: 3220

    Nice question..

    --
    Dineshbabu
    Desire to learn new things..

  • Lokesh Vij

    SSChampion

    Points: 10836

    Easy one for me. Thanks!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kapil_kk

    SSC-Insane

    Points: 21316

    demonfox (3/14/2013)


    cheeky ...

    didn't occur a doubt on the 0 😛

    though I had a doubt over

    select isnull ( convert (char(3),1.0/9) ,'*')

    -- or

    select isnull ( convert (char(3),1/9.0) ,'*')

    and it clears out as it does throws an error

    Msg 8115, Level 16, State 5, Line 1

    Arithmetic overflow error converting numeric to data type varchar.

    thanks for the explanation 🙂

    When Implicit conversion occurs , it gives out the output with default precision of 18,6..

    so 1.0/9 is 0.111111..

    Select select isnull ( convert (char(8),1.0/9) ,'*')

    anything less than 8 would have the arithmetic overflow error for this scenario 😀

    Nice question , though ...

    Thanks for the question ..

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Really a nice and good question.

    one more thing apart from this question. if you set ARITHABORT OFF and then execute below code, it will give output as "*" due to insufficient char length. and if you set char length more than 7, it will give "0.111111".

    It denotes that we need to provide an insufficient length whenever we convert values in char or varchar.

    SET ARITHABORT OFF

    select isnull ( convert (char(3),1.0/9) ,'*')

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

  • kapil_kk

    SSC-Insane

    Points: 21316

    Danny Ocean (3/15/2013)


    Really a nice and good question.

    one more thing apart from this question. if you set ARITHABORT OFF and then execute below code, it will give output as "*" due to insufficient char length. and if you set char length more than 7, it will give "0.111111".

    It denotes that we need to provide an insufficient length whenever we convert values in char or varchar.

    SET ARITHABORT OFF

    select isnull ( convert (char(3),1.0/9) ,'*')

    Thanks Vinay for this... 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Your Welcome Kapil 🙂

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

  • kapil_kk

    SSC-Insane

    Points: 21316

    Danny Ocean (3/15/2013)


    Really a nice and good question.

    one more thing apart from this question. if you set ARITHABORT OFF and then execute below code, it will give output as "*" due to insufficient char length. and if you set char length more than 7, it will give "0.111111".

    It denotes that we need to provide an insufficient length whenever we convert values in char or varchar.

    SET ARITHABORT OFF

    select isnull ( convert (char(3),1.0/9) ,'*')

    Vinay, I tried with ARITHABORT OFF but still I am getting same error "Arithmetic overflow error converting numeric to data type varchar."

    set arithabort off

    select ISNULL(CONVERT(char(3),1.0/9),'*')

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Good question but the correct answer is "0 " (with 2 trailing spaces), not "0".#

    Edit: Add example:

    SELECT REPLACE(ISNULL ( CONVERT (char(3),1/9),'*'),' ','<Space>') AS Ret

    0<Space><Space>

    Best Regards,

    Chris Büttner

  • okbangas

    SSChampion

    Points: 11773

    Really easy question, but the explanation puzzles me. As far as I can see it is no implicit conversion, just a pure integer division.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Stuart Davies

    SSCoach

    Points: 18874

    Nice and easy question to end the week on - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • demonfox

    SSCertifiable

    Points: 6289

    okbangas (3/15/2013)


    Really easy question, but the explanation puzzles me. As far as I can see it is no implicit conversion, just a pure integer division.

    +1

    0.111111 being counted as 0 , is a round off to integer ..

    will that be implicit conversion OP had in mind,0.11111-float to 0 - int? :unsure:

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Carlo Romagnano

    SSC-Insane

    Points: 21877

    okbangas (3/15/2013)


    Really easy question, but the explanation puzzles me. As far as I can see it is no implicit conversion, just a pure integer division.

    +1

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

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