CONVERT BEHAVIOUR

  • hi,

    when I ran query-

    SELECT CONVERT(FLOAT,'-')

    It gives me an error- Error converting data type varchar to float.

    While when I ran query-

    SELECT CONVERT(INT,'-')

    It ran successfully and gave output as 0.

    I am not clear about the behavior of this..

    Can anyone explain me?

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

  • There are a few strings that are treated differently by Int conversions vs Float conversions. It's probably as simple as different teams worked on the two pieces of code, and had some slight difference in the way they dealt with non-numeric ASCII characters.

    I have to ask, why would you want to convert a hyphen to a number of either sort? They Int conversion is probably treating that as if it were "negative 0", which is the same as "zero", mathematically. But why bother with that piece of code?

    - 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

  • i was just playing with CONVERT and faced this issue so just want to know about this weird behavior of float and INT in convert...

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

  • Try converting "1D2" to Int and Float, while you're at it. That's one of the ones I use to show weaknesses in the IsNumeric() function.

    SELECT CAST('1D2' AS FLOAT);

    GO

    SELECT CAST('1D2' AS INT);

    - 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

  • yes,

    SELECT CAST('1D2' AS FLOAT);

    when I ran this query it ran successfully and gives 100 in output.. I dont know how it cast it to 100 :w00t:

    While when I ran this one

    SELECT CAST('1D2' AS INT);

    it gives an error of datatype conversion...

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

  • 1D2 is a version of scientific notation, 1 times 10 to the 2nd power. Try 1.234D3. Try an E instead of a D, too.

    Int doesn't like any of the scientific notation formats, but Float does. That's intentional, so far as I know. Scientific notation and Float are both meant to be non-precise numeric approximations, while Int is meant to be exact.

    Why a hyphen does what you found, I'm not sure. But the two conversion engines do look to me like they were built by different developers, and it may be as simple as that.

    - 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

  • when you run query for:

    select convert(float,'-0')

    it runs successfully

    while if we put any other sign in SELECT CONVERT(INT,'.') other than '+/-' it will gives the same error as float will do....

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

  • Yep.

    - 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

  • My favoured one is:

    SELECT $

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene,

    i tried this it select $ returns 0.00

    while when i tried with other sumbols (!@#$%^&*_+) all returns an error..

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

  • Eugene Elutin (11/6/2012)


    My favoured one is:

    SELECT $

    This also works for £ and € currency symbols. I'm not sure about other currency symbols like Yen, Roubles, etc, as it may depend on the regional settings for the environment or Collation for the DB/Server.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Jason-299789 (11/7/2012)


    Eugene Elutin (11/6/2012)


    My favoured one is:

    SELECT $

    This also works for £ and € currency symbols. I'm not sure about other currency symbols like Yen, Roubles, etc, as it may depend on the regional settings for the environment or Collation for the DB/Server.

    Exactly!

    Guess what

    SELECT POWER(£,$)

    will return? :w00t:

    Proving (at SQL level) that money has no power! :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Also disproving that you cant make something out of nothing, as 0^0 = 1,

    so if i have no money and I raise it to the power of no money I get 1 money. :crazy:

    I think I've just cracked why the banking system collapsed a couple of years ago.:w00t:

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

Viewing 13 posts - 1 through 12 (of 12 total)

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