when save float numbers it converted to IE so how to solve this issue ?

Question

when save float numbers it converted to IE so how to solve this issue ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
June 4, 2022 at 11:05 pm

#4044402
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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

how you see them in SSMS is not how the data is stored - that is an issue with how SSMS displays real/floats.

to see the real content you will need to query directly from a program that does not have that limitation or you need to convert to a decimal first

 select Numbers
 , convert(decimal(38, 17), numbers)
 from #numbersfloat

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 2

thank you for reply

this will solve issue of IE

but it will add extra 0 on right on another values

8 become

8.00000000000000000

1005 become

1005.00000000000000000

so how to handle it please

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

Look up the DECIMAL DATATYPE IN SQL SERVER on your favorite search engine so that you'll understand that all you need to do is change the "scale" and maybe the "precision" of the DECIMAL() datatype.

You should also lookup what the "FLOAT" datatype is because it's a whole lot different that what you probably think it is.

    DROP TABLE IF EXISTS; --Just to make reruns in SSMS easier.
GO
  CREATE TABLE #TestTable
         (
         Numbers DECIMAL(15,7)
         )
;
 INSERT INTO #TestTable
        (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)
;
 SELECT *
   FROM #TestTable
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 4

how to write query detect numbers like IE only

i need to write query detect numbers like IE only

frederico_fonseca SSCoach Points: 16167 More actions · Answer 5

ahmed_elbarbary.2010 wrote:

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

you can only have exponentials if there datatype is a string representing a number - otherwise its always a number and as such it does not have the exponential representation.

so maybe tell us what is exactly your requirement and/or issue you are trying to solve instead of asking for something that may not be what you need.

Steve Collins SSCrazy Points: 2176 More actions · Answer 6

As frederico and Jeff have already explained the numeric value in the table has no display format of its own. If you're ok with a type conversion (from numeric to string) you could try the FORMAT function. FORMAT returns NVARCHAR. Warning: FORMAT is a scalar function with known performance issues. Not sure if there's any good alternative in this case tho. How to return "numbers like IE only"? Idk what this means... perhaps WHERE Numbers<1?

select format(Numbers, 'E2') sci_format,
       sql_variant_property(numbers,'basetype') start_type,
       sql_variant_property(format(numbers, 'e2'),'basetype') converted_to,
       sql_variant_property(format(numbers, 'e2'),'MaxLength') max_len_nvar,
       sql_variant_property(cast(format(numbers, 'e2') as nvarchar(200)),'MaxLength') cast_max_len
from #TestTable;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können