SQL Server Performance

converting from float to varchar

Discussion in 'T-SQL Performance Tuning for Developers' started by JEANLUC, Jun 28, 2005.

  1. JEANLUC New Member

    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
  2. Luis Martin Moderator

    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.



  3. benwilson New Member

    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
  4. ranjitjain New Member

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

    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, &lt; value &gt;, 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. <br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @test FLOAT<br />SELECT @test = 14730.48<br />SELECT @test, LTRIM(RTRIM(STR(ROUND(@test,2),10,2))) <br /> <br />----------------------------------------------------- ---------- <br />14730.48 14730.48<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />See if this helps you. <br /><br />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. <br /><pre id="code"><font face="courier" size="2" id="code"><br />declare @a decimal(18,4)<br />set @a = 0.0003<br />select<br />sum(a)*sum(a)*100,<br />100*sum(a)*sum(a)<br />from (<br />select @a a<br />union all<br />select @a<br />) x<br />go<br /><br />declare @a float<br />set @a = 0.0003<br />select<br />sum(a)*sum(a)*100,<br />100*sum(a)*sum(a)<br />from (<br />select @a a<br />union all<br />select @a<br />) x<br />go<br /><br /> <br />---------------------------------------- ---------------------------------------- <br />.000000 .000036<br /><br />(1 row(s) affected)<br /><br /> <br />----------------------------------------------------- ---------------------- <br />3.5999999999999994E-5 3.5999999999999994E-5<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />As you see, even DECIMAL has its drawbacks. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  6. Luis Martin Moderator

    Well JeanLuc, now you can see why I'm not a developer. <br />This is and answer[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />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, &lt; value &gt;, 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. <br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @test FLOAT<br />SELECT @test = 14730.48<br />SELECT @test, LTRIM(RTRIM(STR(ROUND(@test,2),10,2))) <br /> <br />----------------------------------------------------- ---------- <br />14730.48 14730.48<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />See if this helps you. <br /><br />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. <br /><pre id="code"><font face="courier" size="2" id="code"><br />declare @a decimal(18,4)<br />set @a = 0.0003<br />select<br />sum(a)*sum(a)*100,<br />100*sum(a)*sum(a)<br />from (<br />select @a a<br />union all<br />select @a<br />) x<br />go<br /><br />declare @a float<br />set @a = 0.0003<br />select<br />sum(a)*sum(a)*100,<br />100*sum(a)*sum(a)<br />from (<br />select @a a<br />union all<br />select @a<br />) x<br />go<br /><br /> <br />---------------------------------------- ---------------------------------------- <br />.000000 .000036<br /><br />(1 row(s) affected)<br /><br /> <br />----------------------------------------------------- ---------------------- <br />3.5999999999999994E-5 3.5999999999999994E-5<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />As you see, even DECIMAL has its drawbacks. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  7. Atul New Member

    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)
  8. Luis Martin Moderator

    Atul Welcome to the forum.
    Please check post dates. This one is 8 years old.:)
  9. Atul New Member

    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. :)

Share This Page