How to display two digits after decimal point in SQL Server

Question

I have table which has a column of float data type in SQL Server I want to return my float datatype column value with 2 decimal places.

for ex: if i insert 12.3,it should return 12.30

if i insert 12,it should return 12.00

juergen d · Accepted Answer · 2019-11-05T07:04:49.890

199

select cast(your_float_column as decimal(10,2))
from your_table

decimal(10,2) means you can have a decimal number with a maximal total precision of 10 digits. 2 of them after the decimal point and 8 before.
The biggest possible number would be 99999999.99

edited Nov 05 '19 at 07:04

answered Nov 21 '13 at 11:09

juergen d

201,996
37
293
362

3

THANKS A LOT...IT SAVED ALL MY C# CODE CHANGING TIME.CAN YOU PLEASE EXPLAIN ABOUT WHAT 10 MEANS IN decimal(10,2) – Santosh Nov 21 '13 at 11:29
4

`(10,2)` defines the precision of the decimal value. `10` digits and `2` digits after the comma. – juergen d Nov 21 '13 at 11:35
@juergend hi, i'm trying a simple query like this `select cast(50/20 as decimal(5,2)) from sometable limit 1` but the output it 2.00 (it should be 2.5). – Moosa May 26 '16 at 17:25
1

@Moosa: Because it does the calculation before the casting. And then it already is rounded to `2`. Just use `select 50 / 20.0`. Since `20.0` already is a decimal the conversion works as expected. See http://sqlfiddle.com/#!3/9eecb7/8216 – juergen d May 26 '16 at 22:16
Does this 10 digit precision include only numbers like `12345678.91` or it also include comma like `1,234,5678.91` ? – Shaiju T Mar 20 '19 at 09:35
1

@stom: Only the digits count. – juergen d Mar 20 '19 at 10:47

score 24 · Answer 2 · answered Jun 22 '19 at 21:55

24

You can also do something much shorter:

SELECT FORMAT(2.3332232,'N2')

answered Jun 22 '19 at 21:55

Shiroy

1,648
1
15
22

4

I pref this over the `cast(your_float_column as decimal(10,2))` cause i have no idea how big my decimal can be. – Niels Lucas Mar 10 '20 at 11:03
5

Note that this changes the type to NVARCHAR. – GPHemsley Sep 02 '20 at 04:38

score 9 · Answer 3 · edited Oct 28 '17 at 11:51

9

You can also use below code which helps me:

select convert(numeric(10,2), column_name) as Total from TABLE_NAME

where Total is alias of the field you want.

edited Oct 28 '17 at 11:51

TT.

15,774
6
47
88

answered Oct 28 '17 at 08:56

Bha15

231
3
8

I think it is a other way to do same thing. – Bha15 Oct 28 '17 at 11:24
That's a fair way to put it. – TT. Oct 28 '17 at 11:50

score 5 · Answer 4 · answered Jun 28 '18 at 08:14

5

You can also Make use of the Following if you want to Cast and Round as well. That may help you or someone else.

SELECT CAST(ROUND(Column_Name, 2) AS DECIMAL(10,2), Name FROM Table_Name

answered Jun 28 '18 at 08:14

PatsonLeaner

1,230
15
26

select cast(round(12311313.1,2) as decimal(10,2)) from dual --> 12311313.1 – stackjohnny Feb 14 '19 at 08:53

score 0 · Answer 5 · edited Apr 15 '21 at 04:18

0

select cast(56.66823 as decimal(10,2))

This returns 56.67.

edited Apr 15 '21 at 04:18

Ian Campbell

23,484
14
36
57

answered Apr 14 '21 at 14:19

Kali

31

1

Your answer is correct on what OP (Original Poster) requires but it is not descriptive. Also you are posting on a question that was asked 4 years back and has a correct answer similar and perhaps better than yours. It is good to answer on old post but only if it has an outdated answer. – Aagam Sheth Apr 18 '21 at 05:45

How to display two digits after decimal point in SQL Server

5 Answers5

Linked

Related