How do you write a number with two decimal places for sql server?
This question is related to
sql
sql-server
decimal
number-formatting
If you're fine with rounding the number instead of truncating it, then it's just:
ROUND(column_name,decimals)
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
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.
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
This is how the kids are doing it today:
DECLARE @test DECIMAL(18,6) = 123.456789
SELECT FORMAT(@test, '##.##')
123.46
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
This is how the kids are doing it today:
DECLARE @test DECIMAL(18,6) = 123.456789
SELECT FORMAT(@test, '##.##')
123.46
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.
Try this:
declare @MyFloatVal float;
set @MyFloatVal=(select convert(decimal(10, 2), 10.254000))
select @MyFloatVal
Convert(decimal(18,2),r.AdditionAmount) as AdditionAmount
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;
Try this:
declare @MyFloatVal float;
set @MyFloatVal=(select convert(decimal(10, 2), 10.254000))
select @MyFloatVal
Convert(decimal(18,2),r.AdditionAmount) as AdditionAmount
try this
SELECT CONVERT(DECIMAL(10,2),YOURCOLUMN)
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
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.
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
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
try this
SELECT CONVERT(DECIMAL(10,2),YOURCOLUMN)
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
If you're fine with rounding the number instead of truncating it, then it's just:
ROUND(column_name,decimals)
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
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;
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
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
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.
Source: Stackoverflow.com