converting big float to varchar SQL Server
When I am converting some big floats like:
61326085092
161569087548
1691939680开发者_Python百科80
208082024640
230160816344
after using
Cast(@FloatValue as nvarchar)
I get:
61326100000
161569000000
169194000000
208082000000
230161000000
This is really bad... so How can I get exact values??
The query I am doing is:
INSERT INTO #someT (id,Value) values('''+Cast(@counterI+1 as nvarchar)+''','''+Cast(@Val as nvarchar)+''')';
For the numbers you posted LTRIM(STR(f,32))
works fine.
with floats(f) as
(
SELECT cast(61326085092 as float) union all
SELECT 161569087548 union all
SELECT 169193968080 union all
SELECT 208082024640 union all
SELECT 230160816344 union all
SELECT 230160816344.12
)
select f, CAST(f as nvarchar) as v, LTRIM(STR(f,32)) as s1, LTRIM(STR(f,32,10)) as s2
FROM floats
Returns
f v s1 s2
---------------------- ----------------- -------------- --------------------------------
61326085092 6.13261e+010 61326085092 61326085092.0000000000
161569087548 1.61569e+011 161569087548 161569087548.0000000000
169193968080 1.69194e+011 169193968080 169193968080.0000000000
208082024640 2.08082e+011 208082024640 208082024640.0000000000
230160816344 2.30161e+011 230160816344 230160816344.0000000000
230160816344.12 2.30161e+011 230160816344 230160816344.1200000000
It's because of precision.
Try using Float(53)
or Decimal(38,18)
Are you really using floats? The numbers you posted look like integers...
Cast(cast(@FloatValue as numeric) as nvarchar)
Choose an appropriate numeric
scale and precision for your case. You will always have this problem with floats and reals, they're inherently imprecise.
Are these always whole values(meaning no decimal points) if so convert to bigint instead
declare @f float =230160816344
select convert(bigint,@f)
精彩评论