November 6, 2012 at 7:41 am
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/
November 6, 2012 at 7:46 am
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
November 6, 2012 at 7:49 am
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/
November 6, 2012 at 7:51 am
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
November 6, 2012 at 8:00 am
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/
November 6, 2012 at 8:05 am
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
November 6, 2012 at 8:10 am
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/
November 6, 2012 at 8:23 am
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
November 6, 2012 at 9:27 am
My favoured one is:
SELECT $
November 6, 2012 at 11:58 pm
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/
November 7, 2012 at 1:00 am
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
November 7, 2012 at 1:47 am
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:
November 7, 2012 at 2:05 am
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