Float value converting before concatenate gets rounded in SQL 2008

  • In My table formula(varchar) and value(Float) column.

    Example

    formula --> (A+B)/C

    I am trying to create same expression with value

    But my below example showing value gets rounded if it is big.

    I am trying to do,

    Declare @a float = 123456.235

    SELECT CONVERT( varchar , @a) Result=>123456

    any way to keep same value as It is?

  • Add Style 128 and see if it works for you:

    SELECT CONVERT( varchar , @a, 128)

    Style 128 (along with 126 and 129) is included for legacy reasons and might be deprecated in a future release.

  • for this value @a float=1234556.235 result is 1.234556235E6

    I am not expecting exponent value.

  • What if you try to convert the float to a decimal first? Perhaps that will work for you.

    SELECT CONVERT(varchar, CONVERT( decimal(10,3) , @a))

  • For

    Declare @a float=29123455612589.23610

    SELECT CONVERT(varchar(max), convert(decimal(25,5) , @a))

    Result :29123455612589.23400 which is rounded again.

  • umeshlade87 (3/30/2015)


    For

    Declare @a float=29123455612589.23610

    SELECT CONVERT(varchar(max), convert(decimal(25,5) , @a))

    Result :29123455612589.23400 which is rounded again.

    Did you notice it is not rounded but a different number? If rounded the last number would be 6.

    I believe this is because as BOL says "Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly."

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply