Write a number with two decimal places SQL Server

Question

How do you write a number with two decimal places for sql server?

Answer 1

Try this

SELECT CONVERT(DECIMAL(10,2),YOURCOLUMN)

such as

SELECT CONVERT(DECIMAL(10,2),2.999999)

will result in output 3.00

Answer 2

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.

Answer 3

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

Answer 4

This is how the kids are doing it today:

DECLARE @test DECIMAL(18,6) = 123.456789
SELECT FORMAT(@test, '##.##')

123.46

Answer 5

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

Code screenshot

Answer 6

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

Answer 7

If you're fine with rounding the number instead of truncating it, then it's just:

ROUND(column_name,decimals)

Answer 8

Try this:

 declare @MyFloatVal float;

    set @MyFloatVal=(select convert(decimal(10, 2), 10.254000))

    select  @MyFloatVal

    Convert(decimal(18,2),r.AdditionAmount) as AdditionAmount

Answer 9

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

Answer 10

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;