April 28, 2018 at 4:13 am
Hi,
I ran into an interesting issue yesterday which i managed to resolve but couldn't explain why it worked (which really frustrates me!). So i'm reaching out to see if anyone can help me explain this.
I have a value which i'm expecting to use normal rounding rules when converted to an int (.5 up <0.5 down). I think the reasoning is that monetary values are treated differently (they do rounding) whereas numeric will truncate, but i cant find anything to confirm that in BOL.
The impossible case statement seems to force the datatype output of the else statement (i think) and forces the monetary value in the else to be treated differently in the below scenario.
select $5.69 as TrueValue,
cast(case when 1 = 2 then 0 else $5.69 END as int) as [INT_Case],--Correct
cast(case when 1 = 2 then 0.00 else $5.69 END as int) as [NUMERIC_Case] --Wrong
You can also test similar using the following tests
select cast(5.69 as int) --5
select cast($5.69 as int) --6
declare @mynum numeric(5,2) = 5.69
select cast(@MyNum as int) --5
So, can anyone shed any light on an explanation of confirm the int/numeric/money treatment going on here?
Cheers,
John
April 28, 2018 at 9:09 am
Animal Magic - Saturday, April 28, 2018 4:13 AMHi,I ran into an interesting issue yesterday which i managed to resolve but couldn't explain why it worked (which really frustrates me!). So i'm reaching out to see if anyone can help me explain this.
I have a value which i'm expecting to use normal rounding rules when converted to an int (.5 up <0.5 down). I think the reasoning is that monetary values are treated differently (they do rounding) whereas numeric will truncate, but i cant find anything to confirm that in BOL.
The impossible case statement seems to force the datatype output of the else statement (i think) and forces the monetary value in the else to be treated differently in the below scenario.
select $5.69 as TrueValue,
cast(case when 1 = 2 then 0 else $5.69 END as int) as [INT_Case],--Correct
cast(case when 1 = 2 then 0.00 else $5.69 END as int) as [NUMERIC_Case] --WrongYou can also test similar using the following tests
select cast(5.69 as int) --5
select cast($5.69 as int) --6declare @mynum numeric(5,2) = 5.69
select cast(@MyNum as int) --5So, can anyone shed any light on an explanation of confirm the int/numeric/money treatment going on here?
Cheers,
John
When the data types differ in decimal places, it will truncate or round depending on what data types are involved. Money to Int will round and Numeric to Int will truncate.
The cast and convert documentation has a list towards the end of the Truncate and Rounding results section:
CAST and CONVERT (Transact-SQL)
Sue
April 28, 2018 at 9:19 am
Perfect. Thanks Sue. I did have a look through the cast documentation yesterday but completely overlooked that section.
April 30, 2018 at 6:49 am
Animal Magic - Saturday, April 28, 2018 4:13 AMHi,I ran into an interesting issue yesterday which i managed to resolve but couldn't explain why it worked (which really frustrates me!). So i'm reaching out to see if anyone can help me explain this.
I have a value which i'm expecting to use normal rounding rules when converted to an int (.5 up <0.5 down). I think the reasoning is that monetary values are treated differently (they do rounding) whereas numeric will truncate, but i cant find anything to confirm that in BOL.
The impossible case statement seems to force the datatype output of the else statement (i think) and forces the monetary value in the else to be treated differently in the below scenario.
select $5.69 as TrueValue,
cast(case when 1 = 2 then 0 else $5.69 END as int) as [INT_Case],--Correct
cast(case when 1 = 2 then 0.00 else $5.69 END as int) as [NUMERIC_Case] --WrongYou can also test similar using the following tests
select cast(5.69 as int) --5
select cast($5.69 as int) --6declare @mynum numeric(5,2) = 5.69
select cast(@MyNum as int) --5So, can anyone shed any light on an explanation of confirm the int/numeric/money treatment going on here?
Cheers,
John
Glad you've now seen the documentation on how this works, but I can't help but wonder why you're running into the situation in the first place. One of the better rules to follow when coding T-SQL is to ensure that you know your data types and to CAST or CONVERT first instead of relying on SQL to know what you want. I'm actually unwilling to rely on ANY language where I have to mix data types, so I'll always convert where needed, or when choices are more limited, add or not add a decimal point, as appropriate, to force a result type, because I've effectively changed the data type.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 30, 2018 at 7:24 am
Hi Steve,
Legacy issues really, although complacency plays a part in leaving those issues in. All the monetary data is stored in numeric (generally 10,4s sometimes 8,2s) data types across the whole database. It's rare you would cast these to integers but one business process does do that, and on investigation this led to this post being raised.
Anything in new schema has correct smallmoney datatypes 🙂 I should really get round to amending the old schema too sometime...
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply