Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Write a number with two decimal places SQL Server

People also ask

How do you move a number to 2 decimal places?

Rounding a decimal number to two decimal places is the same as rounding it to the hundredths place, which is the second place to the right of the decimal point. For example, 2.83620364 can be round to two decimal places as 2.84, and 0.7035 can be round to two decimal places as 0.70.

How do I round to 2 places in SQL?

Using ROUND() function with variables and getting the rounded number to the 2 decimal places. Example-6 : Getting the number to the next two decimal places with the operational parameter 1 which says only to truncate the specified number (-23.456) to the given decimal places i.e., 2.

How do I limit decimal places in SQL Server?

The ROUND() function rounds a number to a specified number of decimal places. Tip: Also look at the FLOOR() and CEILING() functions.


Try this

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

such as

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

will result in output 3.00

enter image description here


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.


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


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


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