Float column not show scientific notation

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

General SQL Server Forums

New to SQL Server Programming

Float column not show scientific notation

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2013-10-16 : 05:55:49

We sometimes have small values stored in a column with datatype of float like 0.000644470739403048 which is being converted to -5.8E-05. Perhaps that is OK to be stored in the database however I need the value in decimal format to use. (I'm using longitude values in google maps).

is there anything I can do at the database level. I was looking at the properties which is 53 numeric precision and 8 length.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 08:04:05

float is approximate numeric datatype. If you want to store exact numeric data you should be using Decimal or Numeric. It has maximum precision value of 38 ie maximum 38 digits in value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

insanepaul
Posting Yak Master

178 Posts

Posted - 2013-10-16 : 10:11:10

Ok, if I was to convert it to decimal or numeric, would it affect performance do you think?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-16 : 10:12:32

why convert? why not keep the field as numeric/decimal itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

insanepaul
Posting Yak Master

178 Posts

Posted - 2013-10-16 : 10:19:06

quote:
Originally posted by visakh16

float is approximate numeric datatype. If you want to store exact numeric data you should be using Decimal or Numeric. It has maximum precision value of 38 ie maximum 38 digits in value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks for the info.

insanepaul
Posting Yak Master

178 Posts

Posted - 2013-10-16 : 10:21:18

quote:
Originally posted by visakh16

why convert? why not keep the field as numeric/decimal itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I mean we have a large database and this column is currently a float and it only affects a few rows so I was wondering if changing it to decimal would affect performance. Probably not and I can investigate it. Anyway thanks for your help.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-16 : 11:29:14

You can convert to decimal, but just be careful to investigate who uses those columns and for what purposes, and do sufficient testing. For example, the insertion will fail for the decimal column in the second example. Given that, unless you have a compelling reason to change the data type and unless you are able to do sufficient regression testing, I would be reluctant to change the data type.

CREATE TABLE #tmp(colA DECIMAL(20,3), colB FLOAT);

-- succeeds
INSERT INTO #tmp VALUES ('1.7334343','1.7334343');

-- fails
INSERT INTO #tmp VALUES ('1.7334343E0','1.7334343E0');

-- succeeds
INSERT INTO #tmp VALUES ('1.7334343','1.7334343E0');

DROP TABLE #tmp;

insanepaul
Posting Yak Master

178 Posts

Posted - 2013-10-17 : 05:09:02

quote:
Originally posted by James K

You can convert to decimal, but just be careful to investigate who uses those columns and for what purposes, and do sufficient testing. For example, the insertion will fail for the decimal column in the second example. Given that, unless you have a compelling reason to change the data type and unless you are able to do sufficient regression testing, I would be reluctant to change the data type.
CREATE TABLE #tmp(colA DECIMAL(20,3), colB FLOAT);

-- succeeds
INSERT INTO #tmp VALUES ('1.7334343','1.7334343');

-- fails
INSERT INTO #tmp VALUES ('1.7334343E0','1.7334343E0');

-- succeeds
INSERT INTO #tmp VALUES ('1.7334343','1.7334343E0');

DROP TABLE #tmp;

Thanks for the additioanl info...I'm not going to change the datatype of the column in the table. We use views so I'm going to cast the column as a decimal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 05:21:54

quote:
Originally posted by insanepaul

quote:
Originally posted by James K

You can convert to decimal, but just be careful to investigate who uses those columns and for what purposes, and do sufficient testing. For example, the insertion will fail for the decimal column in the second example. Given that, unless you have a compelling reason to change the data type and unless you are able to do sufficient regression testing, I would be reluctant to change the data type.
CREATE TABLE #tmp(colA DECIMAL(20,3), colB FLOAT);

-- succeeds
INSERT INTO #tmp VALUES ('1.7334343','1.7334343');

-- fails
INSERT INTO #tmp VALUES ('1.7334343E0','1.7334343E0');

-- succeeds
INSERT INTO #tmp VALUES ('1.7334343','1.7334343E0');

DROP TABLE #tmp;

Thanks for the additioanl info...I'm not going to change the datatype of the column in the table. We use views so I'm going to cast the column as a decimal.

Thats a better option

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources