开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜