1

I'm using MSSQL 2014 and I'd like to know if there is a better way to display 2 decimals in a simple division like 10/3.

SELECT 10/3 -- returns 3
SELECT CONVERT(DECIMAL(10, 2), 10/3) -- RETURNS 3
SELECT CAST(10/3 AS DECIMAL(10,2)) -- RETURNS 3

The only way I found to make it work is by casting the divisor as float:

SELECT 10/CAST(3 AS FLOAT) -- returns 3.333333...
SELECT CONVERT(DECIMAL(10, 2), 10/CAST(3 AS FLOAT)) -- RETURNS 3.33
SELECT CAST(10/CAST(3 AS FLOAT) AS DECIMAL(10,2)) -- RETURNS 3.33

Are these two last options the best approach available?. Is it possible to do this without any cast/conversion?

Javier
  • 2,093
  • 35
  • 50
  • SELECT 10 * 1.0 / 3 http://stackoverflow.com/questions/4834973/decimal-values-in-sql-for-dividing-results – Adam Jun 03 '16 at 17:37
  • SELECT CAST(ROUND(10/3.0,2) as DECIMAL(10,2)) ? dont see any significant difference though – mhn Jun 03 '16 at 17:40

3 Answers3

2

Yeah, to truncate you have to specify that you only want two digits. If it were me I'd do:

SELECT cast(10/3.0 as decimal(10,2))
Matt
  • 782
  • 4
  • 11
0

I get a float if I do,

select 10/3.0

https://stackoverflow.com/a/11719098/28045

Community
  • 1
  • 1
johnny
  • 19,272
  • 52
  • 157
  • 259
0

Division of one integer by another will result in an integer. Either cast the numbers or use variables of float or decimal type having values of 10 and 3.

Akshey Bhat
  • 8,227
  • 1
  • 20
  • 20