CONVERT BEHAVIOUR

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » CONVERT BEHAVIOUR

13 posts, Page 1 of 2

1 2 »»

CONVERT BEHAVIOUR

Rate Topic

Display Mode

Topic Options

Author

Message

kapil_kk

Posted Tuesday, November 06, 2012 7:41 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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?

Post #1381569

GSquared

Posted Tuesday, November 06, 2012 7:46 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

kapil_kk

Posted Tuesday, November 06, 2012 7:49 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1381574

GSquared

Posted Tuesday, November 06, 2012 7:51 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

kapil_kk

Posted Tuesday, November 06, 2012 8:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1381586

GSquared

Posted Tuesday, November 06, 2012 8:05 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

kapil_kk

Posted Tuesday, November 06, 2012 8:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1381593

GSquared

Posted Tuesday, November 06, 2012 8:23 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Eugene Elutin

Posted Tuesday, November 06, 2012 9:27 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 11:00 AM
Points: 2,543, Visits: 4,384

My favoured one is:

SELECT $

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1381633

kapil_kk

Posted Tuesday, November 06, 2012 11:58 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1381803

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions