CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Divide integers and round to 3 decimal places with one cast?

Is there a way to divide two integers and round to 3 decimal places with only one cast/convert?

All the examples I see convert to decimal, then round. Some cast both numerator and denominator, etc. I've seen round with floor or ceiling, etc.

I just want to use one cast and I'm done.

For example, 1/3 would be 0.333. 5/3 is 1.667

I'm doing this to cast that result as varchar.

Upvotes: 0

Views: 3523

Answers (1)

Cee McSharpface
Cee McSharpface

Reputation: 8726

Dividing two int values t-sql has only two possible outcomes:

  • error 8134 (division by zero)
  • an int value again

It should be sufficient to coerce one of the operands to numeric:

select round(1.0/3,3,0)

To avoid trailing zeroes, another way:

select cast(1.0/3 as decimal(18,3))

This of course works only with numeric literals. If you have a column or parameter value, a cast is still needed.

This feeble attempt at code golf shaves off one more character:

select convert(decimal(9,3),1.0/3)

Upvotes: 1

Related Questions