CODEWITHSUNDEEP
sql-servert-sqldecimaltype-conversionsqldatatypes
Slider345
Slider345

Reputation: 4758

dividing a decimal type in SQL Server results in unnecessary trailing zeros

I was performing some simple financial calculations in SQL Server when I discovered some odd behavior. I was trying to convert a string of numbers to a decimal type. While the string did not contain a decimal point, I knew from my specifications that the last 3 positions in the string were supposed to be behind the decimal point.

My first approach was flawed, but went something like this:

select convert(decimal(11,3),89456123/1000) as TotalUnits

This resulted in 89456.000. Performing the division before the cast resulted in the decimal parts being truncated.

So I moved the division operation outside the cast, like this:

select convert(decimal(11,3),89456123)/1000 as TotalUnits

This resulted in an explosion of positions after the decimal point. It returned 89456.12300000

According to my decimal specification, I wanted 11 digits, with 3 of them behind the decimal point. Now I have 13 total digits, with 8 behind the decimal. What happened?

To get what I want, I guess I have to double cast, like this:

select convert(decimal(11,3), convert(decimal(11,3),89456123)/1000)

which gives 89456.123.

It turns out no matter what I divide by, the resulting decimal point explosion is the same. Is the division converting the datatype into a double or something?

My question is this: Why is this happening, and is there a more elegant way to compensate for it, instead of double-casting to decimal.

EDIT I found this similar question on SO, but it looks like they are again double-casting.

Upvotes: 1

Views: 1413

Answers (3)

Dudi Konfino
Dudi Konfino

Reputation: 1136

select convert(decimal(11,3),89456123/CONVERT(decimal(11,3),1000))

Upvotes: 0

radar
radar

Reputation: 13425

SQL server does integer arithmetic, to force it to use numeric, you can multiply it by 1.0

No need of using convert twice. This gives 89456.123 with out double convert.

select convert(decimal(11,3),89456123*1.0/1000) as TotalUnits

Upvotes: 2

usr
usr

Reputation: 171178

Why does convert(decimal(11,3),89456123)/1000 end up with 6 decimal places? The rules demand it. numeric division has rather complicated rules about the resulting type.

When you say 1.0 you end up with a numeric with the least scale factors possible to represent this value:

SELECT SQL_VARIANT_PROPERTY(1.11, 'BaseType')
SELECT SQL_VARIANT_PROPERTY(1.11, 'Precision')
SELECT SQL_VARIANT_PROPERTY(1.11, 'Scale')
SELECT SQL_VARIANT_PROPERTY(1.11, 'TotalBytes')

What should you do? I think there is no really elegant solution because of the complicated rules. Any solution I can think of involves rather crazy type inference of intermediate results. I recommend pretty much the same solution that RADAR already gave:

select convert(decimal(11,3), convert(decimal(11, 3), 89456123)/1000) as TotalUnits

The main difference is that I think the *1.0 "trick" used as a short hand for a cast is obfuscating the meaning of the code. If you happen to like it feel free to use it, though.

Upvotes: 2

Related Questions