SQL Server Performance Forum – Threads Archive

converting from float to varchar

I am loosing precision when converting from float to varchar
declare @test float
select @test = 14730.48
select @test
select cast((@test) as varchar(12)) I get the following reasult
14730.5 any ideas?
thanks

I’m not a developer but: declare @test float(25)
declare @test1 decimal(10,2)
select @test = 14730.48
select @test
set @test1 = convert (decimal(10,2), @test)
select cast((@test1) as varchar(12)) HTH
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

From BOL:

quote:Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

I think if you need precision, you should be using a decimal… ben ‘I reject your reality and substitute my own’ – Adam Savage

try this too:
declare @test float
select @test=14730.48
select @test
select convert(varchar(50),cast(@test as money))

To explain a bit. This behaviour is "by design". What happens is that the conversion from FLOAT to VARCHAR uses internally something like CONVERT(VARCHAR, < value >, 0) which might be a legacy problem from those days of only single-precision float types available. SQL Server can represent double-precision float when you use DECLARE … FLOAT(53) There is however a workaround available, that doesn’t need a conversion to another data type. <pre id="code"> DECLARE @test FLOAT SELECT @test = 14730.48 SELECT @test, LTRIM(RTRIM(STR(ROUND(@test,2),10,2))) —————————————————– ———- 14730.48 14730.48 (1 row(s) affected) </pre id="code"> See if this helps you. Also, I don’t agree that DECIMAL is always "better" than FLOAT. It depends on the situation and what you want to do with the data. Here’s an example posted by SQL Server MVP Steve Kass on the MS newsgroups about the differences when using both data types. <pre id="code"> declare @a decimal(18,4) set @a = 0.0003 select sum(a)*sum(a)*100, 100*sum(a)*sum(a) from ( select @a a union all select @a ) x go declare @a float set @a = 0.0003 select sum(a)*sum(a)*100, 100*sum(a)*sum(a) from ( select @a a union all select @a ) x go —————————————- —————————————- .000000 .000036 (1 row(s) affected) —————————————————– ———————- 3.5999999999999994E-5 3.5999999999999994E-5 (1 row(s) affected) </pre id="code"> As you see, even DECIMAL has its drawbacks. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Well JeanLuc, now you can see why I’m not a developer. This is and answer[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis To explain a bit. This behaviour is "by design". What happens is that the conversion from FLOAT to VARCHAR uses internally something like CONVERT(VARCHAR, < value >, 0) which might be a legacy problem from those days of only single-precision float types available. SQL Server can represent double-precision float when you use DECLARE … FLOAT(53) There is however a workaround available, that doesn’t need a conversion to another data type. <pre id="code"> DECLARE @test FLOAT SELECT @test = 14730.48 SELECT @test, LTRIM(RTRIM(STR(ROUND(@test,2),10,2))) —————————————————– ———- 14730.48 14730.48 (1 row(s) affected) </pre id="code"> See if this helps you. Also, I don’t agree that DECIMAL is always "better" than FLOAT. It depends on the situation and what you want to do with the data. Here’s an example posted by SQL Server MVP Steve Kass on the MS newsgroups about the differences when using both data types. <pre id="code"> declare @a decimal(18,4) set @a = 0.0003 select sum(a)*sum(a)*100, 100*sum(a)*sum(a) from ( select @a a union all select @a ) x go declare @a float set @a = 0.0003 select sum(a)*sum(a)*100, 100*sum(a)*sum(a) from ( select @a a union all select @a ) x go —————————————- —————————————- .000000 .000036 (1 row(s) affected) —————————————————– ———————- 3.5999999999999994E-5 3.5999999999999994E-5 (1 row(s) affected) </pre id="code"> As you see, even DECIMAL has its drawbacks. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <hr height="1" noshade id="quote"></blockquote id="quote"> Luis Martin Moderator SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important Bertrand Russell All postings are provided â€œAS ISâ€ with no warranties for accuracy.

You can use this as well:- declare @test float(25)
declare @test1 decimal(10,5)
select @test = 34.0387597207473
select @test
set @test1 = convert(decimal(10,5), @test)
select cast((@test1) as varchar(12)) Select LEFT(cast((@test1) as varchar(12)),LEN(cast((@test1) as varchar(12)))-1)

Atul Welcome to the forum.
Please check post dates. This one is 8 years old.

Hello luis, Thanks

Actually I was struggling with same issue. So got some logic, Thought it might help some other user looking for same issue.

]]>