193

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

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84

14 Answers14

251

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

hc_dev
  • 8,389
  • 1
  • 26
  • 38
  • 1
    I tried SELECT CONVERT(DECIMAL(10,2),10.11111) it returned 10.11 same result for SELECT CONVERT(DECIMAL(10,2),'10.11111') it was in Sql Server 2008 R2 – Nathan Apr 21 '20 at 15:05
  • I get the error message "ERROR: syntax error at or near ",""...referring to the comma right before the column I want to reference. – Bryan Crigger Feb 07 '22 at 23:33
76

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.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • 1
    `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. This is mentioned in the [documentation for `STR`](http://msdn.microsoft.com/en-us/library/ms189527.aspx). Here's a quote from that page: `STR(1223,2) truncates the result set to **.` – Mark Byers May 25 '12 at 09:41
39

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

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
AAA
  • 4,928
  • 1
  • 24
  • 20
  • 5
    It can also be declared as DECIMAL(10, 2). – jrcs3 Jan 14 '09 at 01:54
  • 11
    This is wrong for several reasons. It's not number, it's numeric or decimal. You say numeric(10,2) allows 10 places before the decimal point, which is also wrong. numeric(10,2) allows for 10 total digits with 2 places after the decimal point. Range = -99999999.99 to 99999999.99 – George Mastros Jan 14 '09 at 02:16
  • @G Mastros: It appears you are right on the precision point. On the actual naming of the the convention, in many of SQL implementations NUMBER is a valid type. Although I will admit to not knowing the case of sqlserver – AAA Jan 14 '09 at 02:23
  • @GMastros came to say the same thing. http://msdn.microsoft.com/en-us/library/aa258832%28v=sql.80%29.aspx – Joe Nov 01 '11 at 18:03
29

This is how the kids are doing it today: 

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

123.46

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
Lysoll
  • 644
  • 7
  • 4
  • 2
    FORMAT function is available from version 2012 onwards. – user1263981 Aug 17 '18 at 07:06
  • or: SELECT FORMAT(@test, '.##') – java-love Feb 20 '20 at 18:37
  • 1
    If the input data was: `DECLARE @test DECIMAL(18,6) = 0.456789` then `SELECT FORMAT(@test, '##.##')` returns: `.46` How do you get it to show the leading zero: `0.46` ? – luisdev Jul 09 '20 at 07:27
  • 1
    To get a leading 0, try SELECT FORMAT(@test, '#0.##') – Lysoll Jul 10 '20 at 13:25
  • 3
    I think you would most likely want to use '0.00' instead of '#.##'. The # will ignore trailing zeroes so 5.10 will become 5.1 or 4.00 will just show 4 on its own. The '0.00' format will ensure you always get exactly two decimal places. – dashingdove Mar 09 '21 at 08:36
  • Even though the question is not specific, `FORMAT` does not return a number itr returns a varchar so it only adds to data type confusion here – Nick.Mc Aug 07 '22 at 21:52
20

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

WonderWorker
  • 8,539
  • 4
  • 63
  • 74
Mohamed Ramadan
  • 793
  • 5
  • 15
  • 2
    try the STR() function from Charles Bretana above. It works like your formula but will do rounding on your 25.569 example. – JerryOL Jun 23 '11 at 15:03
16

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
SonalPM
  • 1,317
  • 8
  • 17
7

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

ROUND(column_name,decimals)
antoine
  • 2,036
  • 25
  • 17
1

Try this:

 declare @MyFloatVal float;

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

    select  @MyFloatVal

    Convert(decimal(18,2),r.AdditionAmount) as AdditionAmount
Anil Chaudhary
  • 19
  • 4
1

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

SELECT FORMAT(5634.6334, 'F')
Boris Sokolov
  • 1,723
  • 4
  • 23
  • 38
1

This is an old question, but I use

FORMAT(10, 'N2')

outputs 10.00

Simon Fallai
  • 611
  • 5
  • 5
0

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))
Jephren Naicker
  • 336
  • 1
  • 2
  • 18
0
SELECT FORMAT(104565556.785, '0.00')

Output: 104565556.79

SELECT FORMAT(104565556, '0.00')

Output: 104565556.00
Md Shahriar
  • 2,072
  • 22
  • 11
0

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;
Pete Carter
  • 2,691
  • 3
  • 23
  • 34
kumar vishwash
  • 1
0

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
Taryn
  • 242,637
  • 56
  • 362
  • 405
SQL Master
  • 25
  • 1