Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Julie Zeien » Fun with Decimals
Add to briefcase
11 posts, Page 1 of 2
12»»

Fun with Decimals Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Julie Hargraves
Posted Tuesday, December 15, 2009 10:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 12:15 PM
Points: 3,021, Visits: 458
Comments posted to this topic are about the item Fun with Decimals
Post #834843
Bhavesh_Patel
Posted Tuesday, December 15, 2009 11:17 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
Why answer differs when we are using the same function on same variable?


Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #834861
vk-kirov
Posted Wednesday, December 16, 2009 5:04 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 3,188, Visits: 4,141
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
Post #834991
sknox
Posted Wednesday, December 16, 2009 7:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 1,038, Visits: 1,355
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.
Post #835065
Toreador
Posted Wednesday, December 16, 2009 9:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:52 AM
Points: 1,356, Visits: 4,761
Correct answer, but the explanation is completely wrong!
Post #835191
Julie Hargraves
Posted Wednesday, December 16, 2009 9:20 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 12:15 PM
Points: 3,021, Visits: 458
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.
Post #835224
john.arnott
Posted Thursday, December 17, 2009 11:28 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
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.
Post #835863
hakan.winther
Posted Friday, January 08, 2010 7:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:31 AM
Points: 2,226, Visits: 438
The important point with this question is that you should never take anything for granted and always specify "optional" paramters/statements

/Håkan Winther
MCITP:Database Developer 2008
Post #844313
bvbellomo
Posted Friday, January 08, 2010 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 20, 2011 10:32 AM
Points: 341, Visits: 2,079
A better question would have been, what is the result of:

SELECT CONVERT(DECIMAL, 1.2)
Post #844597
L' Eomot Inversé
Posted Saturday, February 06, 2010 6:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:10 PM
Points: 7,084, Visits: 7,137
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #861229
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.