Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Converting Float to Numeric

    Hello guys! I am new to the forums and would like to post a question to you!

    I have a problem rounding a floating number in MSSQL 2005.

    In fact, I need the floating point number to be displayed with a comma like the following example illustrates:

    432,900010347366 needs to be converted to 432,90

    I tried CONVERT(NUMERIC(9, 3),"432,900010347366")) as ConNum which works. However, sometimes I receive an error - overflow "converting float to numeric".

    Maybe you have an idea?

    Best regards,

    FreezerSE

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    Code:
    DECLARE	@Sample FLOAT
    
    SET	@Sample = 432.900010347366
    
    SELECT	@Sample,
    	CAST(@Sample AS NUMERIC(9, 2))

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    NUMERIC(9, 3) allows you to have integer part between -999999 and 999999. Larger or smaller values will error out.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    CONVERT(NUMERIC(9, 3),"432,900010347366"))
    I notice quotes " " around 432,900010347366
    and you use a comma instead of a point

    SELECT CONVERT(NUMERIC(9, 2), 432.900010347366)
    gives 432.90

    But I prefer using CAST and DEC

    SELECT CAST(432.900010347366 AS DEC(9,2))
    gives 432.90

    When you try to do that on a float that is too big to fit in a DECIMAL (9, 2) number you get an error
    SELECT CAST(987654321.123456789 AS DEC(9,2))
    gives "Arithmetic overflow error converting numeric to data type numeric."
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2009
    Posts
    3
    Hey guys! Thanks for your answers! I appreciate it!

    I tried the following before posting here and consequently receive the arithmetic overflow error! Is there a way around it?

    CAST(sum(fm_gekauft*PreisStehend) AS DEC(9,4)) as waldbesitzer

    And yes, I definetly need the comma instead of the point! I use excel to run reports based on the results and it seems to be a little choosy on the point! At least when you consider that it's a European version!

    Do you have any idea how to solve that problem? It's driving me nuts as I already spend hours of trying different attempts!

    Greetz, FreezerSE

  6. #6
    Join Date
    Apr 2007
    Posts
    183
    CAST(sum(fm_gekauft*PreisStehend) AS DEC(20,4)) as waldbesitzer

  7. #7
    Join Date
    Mar 2009
    Posts
    3
    @Peso

    Yes, that's the way! Superb! Is there however a way to display 12345.67 as 12345,67? I tried converting it to numeric but it also delivers a decimal point! Sorry for those dumb questions! I have to admit I am fairly new to these parts of t-sql!

    Greetz, FreezerSE

  8. #8
    Join Date
    Mar 2009
    Posts
    3
    You can just try to use replace function.

    Something like this :
    Select replace('12345.67','.',',')

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by FreezerSE
    Is there however a way to display 12345.67 as 12345,67?
    Get your front end to do this. SQL can do it, as Jav has shown, but it is typically considered poor practice. This is no longer a decimal number - it is a string of digits with a comma in there. Have SQL return and pass a decimal, let your front end do any changes you want to prettify things.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    The European separators seem silly.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Even to me, and I hail from the Confederation of European Nations.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •