# Thread: Converting Float to Numeric

1. Registered User
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. Registered User
Join Date
Apr 2007
Posts
183
Code:
```DECLARE	@Sample FLOAT

SET	@Sample = 432.900010347366

SELECT	@Sample,
CAST(@Sample AS NUMERIC(9, 2))```

3. Registered User
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. Registered User
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."

5. Registered User
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. Registered User
Join Date
Apr 2007
Posts
183
CAST(sum(fm_gekauft*PreisStehend) AS DEC(20,4)) as waldbesitzer

7. Registered User
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. Registered User
Join Date
Mar 2009
Posts
3
You can just try to use replace function.

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

9. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
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.

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

11. King of Understatement
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.

#### Posting Permissions

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