SQLTeam.com | Weblogs | Forums

Small numbers on float data type converted to IE why and how to solve it?

I work on sql server 2017 i face issue when save data on float column data type

it saved as IE-07 AND ie-05 so what this and how to handle it please

how to save data on float numbers as it is

if there are any way to save data on correct way on another data type please tell me

my sample data

create table #numbersfloat
 (
 Numbers float
 )
 insert into #numbersfloat(Numbers)
 values
 (0.0000001),
 (0.00001),
 (0.0000001),
 (0.00001),
 (0.0000001),
 (0.00001),
 (0.0000001),
 (0.00001),
 (10000000),
 (8),
 (1),
 (10000000),
 (14),
 (10000000),
 (1005)

when i make select from table i see issue on data as below

so how to solve it
Numbers
1.00E-07
1.00E-05
1.00E-07
1.00E-05
1.00E-07
1.00E-05
1.00E-07
1.00E-05
10000000
8
1
10000000
14
10000000
1005

SELECT CAST(Numbers AS decimal(30, 10)) AS NumbersFROM #numbersfloat

OR

create table #numbersdecimal
(
Numbers decimal(30, 10)
)

but data on float type will change
as example
8 will be 8.00000
i don't need add 0 on right of values
i need another data to still as it is without change

how to write query detect numbers like IE only
i need to write query detect numbers like IE only

Don't use float. It's an approximate data type, if the number is small enough it will display in that notation. Either CAST as decimal/numeric or use the FORMAT() or STR() functions to convert them to strings that match the format you want.