Converting float to decimal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Converting float to decimal

Author

Topic

muniappank
Starting Member

7 Posts

Posted - 2010-05-20 : 14:01:17

My table column datatype is float. When I am doing decimal convertion in the selection, It is giving wrong value or error. This column is having values like '4.11111111111111E+165'. Is there any idea to make it work?. This is the production database. Can I change the column datatype to decimal without loosing data?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 14:25:43

That value is too big for decimal data type even if we use DECIMAL(38,38):

declare @f float

set @f = 4.11111111111111E+165

SELECT CONVERT(decimal(38,38), @f)

quote:

Msg 8115, Level 16, State 6, Line 5
Arithmetic overflow error converting float to data type numeric.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-20 : 14:57:13

Actually, the sample number provided isn't really a decimal. It's a huge whole number. This is what it looks like in "regular" notation:

4,111,111,111,111,110,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000

The maximum value you can put in a DECIMAL(38,0) is:
99,999,999,999,999,999,999,999,999,999,999,999,999

The maximum storable in a BIGINT is:
9,223,372,036,854,775,807

It is possible to use a function similar to the ones I created here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=95322

To test the value before conversion and supply a default if it is outside the bounds of what DECIMAL can provide:

declare @f float
set @f = 4.11111111111111E+165
SELECT CONVERT(DECIMAL(38,0),CASE dbo.udfIsValidDECIMAL(@f,38,0) WHEN 1 THEN @f ELSE NULL END)

Unfortunately, my function doesn't support float because it rejects scientific notation. You would first have to expand the float to regular notation before passing it into the function.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-20 : 15:08:43

This must be bad data then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

muniappank
Starting Member

7 Posts

Posted - 2010-05-21 : 11:53:05

Thank you all for reply. It is the error related to bad data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources