CODEWITHSUNDEEP
sqlsql-serverdecimalnumber-formatting
student2009
student2009

Reputation:

Write a number with two decimal places SQL Server

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

Upvotes: 207

Views: 1176781

Answers (13)

Md Shahriar
Md Shahriar

Reputation: 2736

SELECT FORMAT(104565556.785, '0.00')

Output: 104565556.79

SELECT FORMAT(104565556, '0.00')

Output: 104565556.00

Upvotes: 0

Jephren Naicker
Jephren Naicker

Reputation: 356

Another way to try below, e.g 60/100 = 0.6, SELECT CONVERT(DECIMAL(10,2),(60*0.01)) = 0.60

  SELECT CONVERT(DECIMAL(10,2),(YourValue*0.01))

Upvotes: 1

Simon Fallai
Simon Fallai

Reputation: 671

This is an old question, but I use

FORMAT(10, 'N2')

outputs 10.00

Upvotes: 5

Boris Sokolov
Boris Sokolov

Reputation: 1793

The easiest way to have two decimals is SQL Format with "F" parameter:

SELECT FORMAT(5634.6334, 'F')

Upvotes: 3

Manoj
Manoj

Reputation:

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

Upvotes: 262

Lysoll
Lysoll

Reputation: 744

This is how the kids are doing it today: 

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

123.46

Upvotes: 35

Anil Chaudhary
Anil Chaudhary

Reputation: 19

Try this:

 declare @MyFloatVal float;

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

    select  @MyFloatVal

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

Upvotes: 1

SonalPM
SonalPM

Reputation: 1337

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

Upvotes: 18

Mohamed Ramadan
Mohamed Ramadan

Reputation: 803

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

Upvotes: 21

antoine
antoine

Reputation: 2106

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

ROUND(column_name,decimals)

Upvotes: 8

AAA
AAA

Reputation: 4956

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

Upvotes: 42

Charles Bretana
Charles Bretana

Reputation: 146499

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.

Upvotes: 78

kumar vishwash
kumar vishwash

Reputation: 1

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;

Upvotes: 0

Related Questions