SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with Decimals


Fun with Decimals

Author
Message
Julie Hargraves
Julie Hargraves
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3095 Visits: 461
Comments posted to this topic are about the item Fun with Decimals
Bhavesh_Patel
Bhavesh_Patel
Say Hey Kid
Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)Say Hey Kid (687 reputation)

Group: General Forum Members
Points: 687 Visits: 297
Why answer differs when we are using the same function on same variable?



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3740 Visits: 4408
Explanation
When precision is not specified with the decimal type, TSQL will use whatever precision would use the minimum space to store with the number it is converting to.

This is not correct. SQL Server does not use "whatever precision" in this case. Here is the quote from BOL about precision (http://msdn.microsoft.com/en-us/library/ms187746.aspx):
decimal and numeric (Transact-SQL)

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

So when precision is not specified, SQL Server uses the value = 18.

Another quote from BOL:
s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. ... The default scale is 0; therefore, 0 <= s <= p.


Thus the expression "CONVERT(DECIMAL, @var)" is equal to "CONVERT(DECIMAL(18,0), @var)".

The batch from the example is equal to:
DECLARE @TestDecimal DECIMAL(8, 2)
SET @TestDecimal = 275953.00
SELECT CONVERT( DECIMAL(18,0), @TestDecimal * 0.40 ) ,
CONVERT( DECIMAL(18,0), @TestDecimal ) * 0.40



Bhavesh_Patel
Why answer differs when we are using the same function on same variable?

In the first expression, the result of multiplication is converted to DECIMAL:
CONVERT(DECIMAL, @TestDecimal * 0.40) = CONVERT(DECIMAL(18,0), 275953.00 * 0.40) = CONVERT (DECIMAL(18,0), 110381.2000) = 110381

In the second expression, the multiplier is converted to DECIMAL:
CONVERT(DECIMAL, @TestDecimal) * 0.40 = CONVERT(DECIMAL(18,0), @TestDecimal) * 0.40 = 275953 * 0.40 = 110381.20
sknox
sknox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2653 Visits: 2833
Bhavesh_Patel (12/15/2009)
Why answer differs when we are using the same function on same variable?

Because we're not actually running the function on the same variable. In the first column, we're doing the following:

CONVERT(DECIMAL, @TestDecimal * 0.40)
Procedurally, this is
1. Multiply @TestDecimal by 0.40
2. Convert the result to DECIMAL, using the defaults (precision 18, scale 0). This is where the rounding occurs.

In column 2, we're doing
CONVERT(DECIMAL, @TestDecimal) * 0.40
Procedurally, this is
1. Convert the value in @TestDecimal to DECIMAL, using the defaults (precision 18, scale 0). Since there's nothing to the right of the decimal, the value is not changed (although the data type is.)
2. Multiply this by 0.40. This creates a resulting value with digits to the right of the decimal point. See http://technet.microsoft.com/en-us/library/ms190476.aspx for details on how SQL Server determines the precision and scale of a result of a mathematical operation on DECIMAL values.
Toreador
Toreador
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2817 Visits: 8084
Correct answer, but the explanation is completely wrong!
Julie Hargraves
Julie Hargraves
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3095 Visits: 461
I apologize for the incorrect explanation. I ran into a problem with this sort of thing about 3 months ago, and I looked all over BOL EXCEPT for the decimal data type page. Oops. I thought this was an undocumented problem, so I played with it for a couple hours and came to an incorrect conclusion. Hopefully Steve can fix it. Thank you to those who knew the correct reason behind this.
john.arnott
john.arnott
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2028 Visits: 3059
Julie, I've been through a similar experience posting a QOD and then finding out that I'd missed a point. Don't fret over that.

But, more important than a statement of the correct explanation for your results, folks should realize that NEITHER example is really a correct construction. You said it yourself in the explanation, so let's see the real effect of not specifying precision. This query:
DECLARE @TestDecimal DECIMAL(8, 2)
SET @TestDecimal = 275953.73
SELECT CONVERT( DECIMAL, @TestDecimal * 0.40 )
, CONVERT( DECIMAL, @TestDecimal ) * 0.40
, CONVERT( DECIMAL(8,2), @TestDecimal * 0.40 )


returns
110381   110381.60   110381.49

Only the last column, the one produced with an explicit precision, is correct. The CONVERT in the second column rounds the local variable 275953.73 to 275954 before doing the multiplication by 0.40.
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2865 Visits: 608
The important point with this question is that you should never take anything for granted and always specify "optional" paramters/statements

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
bvbellomo
bvbellomo
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 2079
A better question would have been, what is the result of:

SELECT CONVERT(DECIMAL, 1.2)
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14604 Visits: 12238
Julie Zeien (12/16/2009)
I apologize for the incorrect explanation. I ran into a problem with this sort of thing about 3 months ago, and I looked all over BOL EXCEPT for the decimal data type page. Oops. I thought this was an undocumented problem, so I played with it for a couple hours and came to an incorrect conclusion. Hopefully Steve can fix it. Thank you to those who knew the correct reason behind this.

Unfortunately it's very easy play with something for a while, form a mental model of what's going on, then try a few tests to check the model and when they work believe it is true. So don't worry to much at having done it - we all do it sometimes. The great trick is to find some tests that will check all the edge conditions implied by the model, which is sometimes very hard to do.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search