Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CONVERT BEHAVIOUR Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 7:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1381569
Posted Tuesday, November 6, 2012 7:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1381571
Posted Tuesday, November 6, 2012 7:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1381574
Posted Tuesday, November 6, 2012 7:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1381577
Posted Tuesday, November 6, 2012 8:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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
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/
Post #1381586
Posted Tuesday, November 6, 2012 8:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1381592
Posted Tuesday, November 6, 2012 8:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1381593
Posted Tuesday, November 6, 2012 8:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1381597
Posted Tuesday, November 6, 2012 9:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
My favoured one is:

SELECT $



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

How to post your question to get the best and quick help
Post #1381633
Posted Tuesday, November 6, 2012 11:58 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1381803
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse