cMinor cMinor - 2 months ago 16
SQL Question

fetch correct number SQL Server

I have a table:

id Number
--------------------
1 61326085092
2 161569087548
3 169193968080
4 208082024640
5 230160816344
6 218557943068


I am declaring a float variable:

DECLARE @varible AS FLOAT


when I set a variable with the result of a query to the table described:

SELECT @variable = Number FROM @TableR where ID = 1;


I am getting:
variable: 6.13261e+010

So this is annoying because As I am computing with big values, I am losing some numbers...
How do I fetch the result as it is
61326085092
, not
6.13261e+010

Could this be a varchar-float convertion error?

so for example:

DECLARE @var AS FLOAT
set @var = 6.13261e+010 * 6.13261e+010
PRINT LTRIM(STR(@var,32,15))

set @var = 61326085092 * 61326085092
PRINT LTRIM(STR(@var,32,15))


results:

3760890541210000200000.000000000
3760888712711224400000.000000000


very different...

Answer

Have you tried specifying the precision, eg: see here

SELECT CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2)))

or

SELECT STR(@testFloat, 38, 2)
Comments