May 6, 2022 at 5:16 pm
Part of the problem is that SQL Server implicitly converts the results to a higher precision than you specify for one or both of the operands. If it didn't do that you would end up with invalid results due to rounding and/or truncation.
The double-cast isn't necessary here - you could do the following:
declare @WeightInGrams int
set @WeightInGrams = 164
select @WeightInGrams,
CAST(@WeightInGrams / 1000.0 AS decimal(18,3))
If your divisor is a fixed value - either a parameter or column, you could do something like this:
declare @WeightInGrams int = 164
, @divisor int = 1000;
select @WeightInGrams,
CAST(@WeightInGrams / (@divisor * 1.0) AS decimal(18,3))
With that said - the above constructs will include implicit converts so you can't get away from the actual conversion, but you can avoid explicitly coding each conversion. The implicit conversion for an integer data type will be to a numeric(10,0) - and since a division of the largest integer can never be larger than 10 digits then converting to a numeric that allows 15 digits to the left of the decimal isn't necessary.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply