Write a number with two decimal places SQL Server
SqlSql ServerDecimalNumber FormattingSql Problem Overview
How do you write a number with two decimal places for sql server?
Sql Solutions
Solution 1 - Sql
Try this
SELECT CONVERT(DECIMAL(10,2),YOURCOLUMN)
such as
SELECT CONVERT(DECIMAL(10,2),2.999999)
will result in output 3.00
Solution 2 - Sql
Use Str() Function. It takes three arguments(the number, the number total characters to display, and the number of decimal places to display
Select Str(12345.6789, 12, 3)
displays: ' 12345.679' ( 3 spaces, 5 digits 12345, a decimal point, and three decimal digits (679). - it rounds if it has to truncate, (unless the integer part is too large for the total size, in which case asterisks are displayed instead.)
for a Total of 12 characters, with 3 to the right of decimal point.
Solution 3 - Sql
Generally you can define the precision of a number in SQL by defining it with parameters. For most cases this will be NUMERIC(10,2) or Decimal(10,2) - will define a column as a Number with 10 total digits with a precision of 2 (decimal places).
Edited for clarity
Solution 4 - Sql
This is how the kids are doing it today:
DECLARE @test DECIMAL(18,6) = 123.456789
SELECT FORMAT(@test, '##.##')
123.46
Solution 5 - Sql
This work for me and always keeps two digits fractions
23.1 ==> 23.10
25.569 ==> 25.56
1 ==> 1.00
Cast(CONVERT(DECIMAL(10,2),Value1) as nvarchar) AS Value2
Solution 6 - Sql
If you only need two decimal places, simplest way is..
SELECT CAST(12 AS DECIMAL(16,2))
OR
SELECT CAST('12' AS DECIMAL(16,2))
Output
12.00
Solution 7 - Sql
If you're fine with rounding the number instead of truncating it, then it's just:
ROUND(column_name,decimals)
Solution 8 - Sql
Try this:
declare @MyFloatVal float;
set @MyFloatVal=(select convert(decimal(10, 2), 10.254000))
select @MyFloatVal
Convert(decimal(18,2),r.AdditionAmount) as AdditionAmount
Solution 9 - Sql
This will allow total 10 digits with 2 values after the decimal. It means that it can accomodate the value value before decimal upto 8 digits and 2 after decimal.
To validate, put the value in the following query.
DECLARE vtest number(10,2);
BEGIN
SELECT 10.008 INTO vtest FROM dual;
dbms_output.put_line(vtest);
END;
Solution 10 - Sql
Multiply the value you want to insert (ex. 2.99) by 100
Then insert the division by 100 of the result adding .01 to the end:
299.01/100