January 21, 2009 at 7:45 am
This isn't urgent, because I addressed the problem. What I'm curious about is why this has to be done the way it is.
Anyway, I've got a calculation that converts KB to MB for a report, so I take a provided float value and divide it by 1024. The developer wants the query output to yield two decimal places of precision, so I'm CASTing the result to a decimal, like so:
SELECT ... CAST((SUM([field])/1024) AS DECIMAL(8,2))
This gives an arithmetic overflow error when [field] contains a large enough number. One example:
SELECT CAST((30000000000/1024) as DECIMAL(8,2))
Results in an overflow error. Upping the scale of the decimal corrects the problem, so:
SELECT CAST((30000000000/1024) as DECIMAL(10,2))
Works. We've got space for 10 digits left of the radix, so it's no problem doing this. I'm just not sure what the order of operations is such that DECIMAL(8,2) isn't large enough - the result of the calculation (29,296,875) should fit, so it's clearly either doing something I'm unaware of, or doing something in an order I don't expect.
I'm mildly baffled, and I'm not able to explain to the developer exactly why it has to be the way it does...
______
Twitter: @Control_Group
January 21, 2009 at 7:57 am
Decimal 8,2 means you have a total of 8 digits, not 8 to the left of the decimal point. Therefore, if you break a million (7 digits, you only have 6 to the left of the decimal), you've overflowed.
Observe:
select cast(1000000 as decimal(8,2))
January 21, 2009 at 8:07 am
Garadin (1/21/2009)
Decimal 8,2 means you have a total of 8 digits, not 8 to the left of the decimal point. Therefore, if you break a million (7 digits, you only have 6 to the left of the decimal), you've overflowed.Observe:
select cast(1000000 as decimal(8,2))
*forehead slap*
D'oh
I even knew that. Thanks.
______
Twitter: @Control_Group
January 21, 2009 at 8:09 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply