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 1234»»»

Casting question Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 8:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 2:05 AM
Points: 47, Visits: 18
Comments posted to this topic are about the item Casting question
Post #1431332
Posted Thursday, March 14, 2013 8:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:05 PM
Points: 5,566, Visits: 24,734
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

Before posting a performance problem please read
Post #1431333
Posted Thursday, March 14, 2013 10:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
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 :D

Nice question , though ...

Thanks for the question ..


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1431350
Posted Thursday, March 14, 2013 10:38 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:36 AM
Points: 926, Visits: 556
Nice question..

--
Dineshbabu
Desire to learn new things..
Post #1431352
Posted Thursday, March 14, 2013 10:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Easy one for me. Thanks!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1431353
Posted Thursday, March 14, 2013 11:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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 :D

Nice question , though ...

Thanks for the question ..



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1431360
Posted Friday, March 15, 2013 12:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
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 !!!
www.GrowWithSql.com

Post #1431365
Posted Friday, March 15, 2013 12:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1431367
Posted Friday, March 15, 2013 12:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Your Welcome Kapil



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1431368
Posted Friday, March 15, 2013 12:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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

Add to briefcase 1234»»»

Permissions Expand / Collapse