How to convert float into 2 decimal places

Today, I have to check the data which is generate by my program against the record that the company had, to see if my program works properly, so that we can upgrade the process.

One of the tricky part of that process is to covert one of the SQL field into 2 decimal places. I did some research online, finally I found a solution for that.

It looks quite simple:

What you need to do is to convert the float/money into a decimal/numeric by using the CAST Keyword on SQL Server.

Here it is the example.

decimal[(p[, s])] and numeric[(p[, s])]

DECLARE @NumToConvert FLOAT

SET @NumToConvert = 123.56789

SELECT CAST(@NumToConvert AS DECIMAL(10,3))

–CAST([*Number to convert] AS DECIMAL([*Precision], [*Scale]))

*Number to Convert = The number which you want to convert from

*Precision = is the number of character(Number) you have from the number that you want to convert from

*Scale = is the number of decimal place you want to have.

Normal I would set the *Precision to 20, to make sure the size of the number is less than the precision we set. 20 should be big enough. Otherwise you may receive an error “Arithmetic overflow error converting float to data type numeric.”

For more information about Decimal or Numeric please visit the following URL

http://msdn.microsoft.com/en-us/library/aa258832(SQL.80).aspx

Please correct me if my concept is wrong.

Leave a Reply