How we can Convert large size of decimal values to float and without exponential values

Question

When I try to convert values from decimal to float, they are shown with exponential values

When I converted numeric to decimal values then they show with .00 fractional, but I required only in numeric.

check blow mention query.

select  cast(CAST(435468867990778789.89 as decimal(35, 2))as float) as APPARENTWACTIMP
select  CAST(43546886799090787 as decimal(35, 2))as APPARENTWACTIMP
select  CAST(43546886799090787 as decimal(35, 2))as APPARENTWACTIMP
select CAST(43546886799090787.89 as float)

Above Query result are

4.35468867990779E+17
43546886799090787.00
43546886799090787.00
4.35468867990908E+16

But I require a result like "435468867990778789.89".

Possible duplicate of [convert float into varchar in SQL server without scientific notation](https://stackoverflow.com/questions/6521354/convert-float-into-varchar-in-sql-server-without-scientific-notation) — CodeCaster, Dec 04 '17 at 12:04
How come you want `435468867990778789.89` when you do `CAST(43546886799090787 as decimal(35, 2))`? — jarlh, Dec 04 '17 at 12:09
Input is not the same. select CAST(43546886799090787.89 as decimal(35, 2))as APPARENTWACTIMP — Kiki, Dec 04 '17 at 12:15
input are correct ,my question is that i need decimal values in decimal and numeric values in only in numeric .not required fractional part in numeric like .00 — Lalit Sharma, Dec 04 '17 at 12:29
Be clear that what is shown to you when some tool (such as SSMS) converts a value into a string for display purposes has *nothing* to do with what is *actually* being stored in these numeric datatypes. Also be clear that there is a limited precision in all of these data types so expecting to be able to store 18 or so digits accurately in a data type with only [15 digits of precision](https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql) is unlikely to work out well. — Damien_The_Unbeliever, Dec 04 '17 at 12:49
@LalitSharma have u tried to cast it like `select cast(cast(435468867990778789.89 as float) as bigint)` — Yogesh Sharma, Dec 04 '17 at 13:05
Why do you want to convert to float? float by definition is imprecise; whereas decimal with precision and scale maintains precision that float does not; float sacrifices precision for performance. — xQbert, Apr 25 '18 at 13:25
and now that the server's back up: A good read on the differences between float, decimal in SQL Server: https://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server/7158770#7158770 Simply put: use Decimal unless you are dealing with really large or really small numbers which typically require scientific notation where being off a few factors has no real bearing on your calculations. — xQbert, Apr 25 '18 at 13:48

Monah · Answer 1 · 2017-12-04T13:44:35.690

2

You can use the following

declare @testFloat decimal(38,2) = 435468867990778789.89
SELECT CONVERT(varchar, @testFloat)

OR

SELECT cast(@testFloat as varchar)

Result : 435468867990778789.89

Edit

Based on the comment: you can do the following

Explanation

Split the string into two parts, before dot and after dot.
Then concat both result by taking only two characters after the dot only.

Query

declare @testFloat numeric(38,6) = 435468867990778789.89656,
        @string varchar(100) 
set @string = cast(@testFloat as varchar)

select @string = substring(@string,0,charindex('.',@string,0)) 
                 + '.' 
                 + substring(@string,charindex('.',@string,0)+1,2)

select @string

Result : 435468867990778789.89

Hope this will help you

edited Dec 04 '17 at 13:44

answered Dec 04 '17 at 13:13

Monah

6,714
6
22
52

Hi thanks for solution but want to truncate values by 2 decimal if decimal fractional more than 2 – Lalit Sharma Dec 04 '17 at 13:28
I edited my answer to include your requirement, hope it will help you – Monah Dec 04 '17 at 13:39

score 1 · Answer 2 · answered Dec 04 '17 at 12:31

1

Try this

select try_parse('43546886799090787.89' as numeric(35,2) using 'en-US')

answered Dec 04 '17 at 12:31

SqlKindaGuy

3,501
2
12
29

thanks but only numeric values showing like 43546886799090787.00 ,i not need .00 fractional values . select try_parse('43546886799090787' as numeric(35,2) using 'en-US') – Lalit Sharma Dec 04 '17 at 12:40
you might need a case statement..Please find my answer below. – Shammas Dec 04 '17 at 13:01

Shammas · Answer 3 · 2017-12-04T13:01:04.537

1

Could you try the below query?.

You might need a case statement for this

select case when ceiling(43546886799090787.89) = floor(43546886799090787.89) 
       then      CONVERT(varchar, CAST(43546886799090787.89 as decimal))
       else      CONVERT(varchar, 43546886799090787.89) 
       end 
o/p --> 43546886799090787.89

select case when ceiling(43546886799090787.00) = floor(43546886799090787.00) 
   then      CONVERT(varchar, CAST(43546886799090787.00 as decimal))
   else      CONVERT(varchar, 43546886799090787.00) 
   end
o/p --> 43546886799090787

If you want to round the value to nearest whole number, use the below script.

select  CAST(CAST(43546886799090787.89 AS decimal) AS varchar)
--O/P -> 43546886799090788

Please check the script and let me know if you face any issues.

edited Dec 04 '17 at 13:01

answered Dec 04 '17 at 12:44

Shammas

381
1
4
15

`select case when ceiling(43546886799090787.89) = floor(43546886799090787.89) then CONVERT(varchar, CAST(43546886799090787.89 as decimal)) else CONVERT(varchar, 43546886799090787.89) end o/p --> 43546886799090787.89` – Lalit Sharma Dec 04 '17 at 13:13
Hi Lalit..Did the above script work for you?. Please let me know if you face any issues with the script – Shammas Dec 04 '17 at 14:21
It will be great if you could accept the correct answer among the answers and upvote the answers that you find helpful.This is because other users who face similar issues in future might find it helpful. – Shammas Dec 04 '17 at 14:29

How we can Convert large size of decimal values to float and without exponential values

3 Answers3