Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Decimal Conversions in SQL Server Change Datatype Format

MSSQLTips author Thomas LaRock By:   |   Read Comments (7)   |   Related Tips: More > Data Types
Problem

When performing simple calculations inside of SQL Server using decimals, you can observe what may seem as odd behavior with the resulting datatype. Instead of returning the desired decimal format SQL Server generates an entirely different decimal format. In this tip we will look at the issue and how to resolve this.

Solution

To resolve this problem you can use the CAST or CONVERT functions in order to ensure that you are returning the correct datatype. I will walk through an example of the issue and how this can be resolved.


SQL Server provides many different datatypes for your use. Many people who are new to SQL Server may not be aware that depending upon their code they may end up having implicit conversions resulting in unexpected results. For example, the following code is something you might come across at one time or another:

DECLARE @first decimal(18,2), @second decimal(18,2)
SET @first = 1234567891234567.87
SET @second = 222.13
SELECT @first/@second as 'Result'

The end result of this code is as follows:

Result
5557862023295.22293251699455273938

The result is no longer a decimal(18,2) datatype. This may or may not be a problem, depending on the nature of the application and what happens after the result is returned. However, this is often the time when I will hear someone comment about how SQL Server "isn't working right" and point to the result and then tell the DBA to "go fix the problem".

The truth is that MS SQL is working exactly as expected, although finding the documentation for this behavior can be difficult. In this particular case you want to examine the MSDN entry for "Precision, Scale, and Length" http://msdn.microsoft.com/en-us/library/ms190476.aspx .

Reviewing the details in the document mentioned above, you will find that the behavior is exactly as expected. In the example above we are doing division of two identical decimal(18,2) datatypes. The table at http://msdn.microsoft.com/en-us/library/ms190476.aspx shows us the formula for division on the fourth line in the table.

In our example we have both p1 and p2 = 18 and both s1 and s2 = 2. That means the precision for our result will be as follows based on the formula found in the MSDN article mentioned above:

p1 - s1 + s2 + max(6, s1 + p2 + 1) = 18 - 2 + 2 + max(6, 2 + 18 + 1) = 18 + 21 = 39

However, the maximum allowed precision is 38 (look for the note at the bottom of the MSDN article).

Further, the scale for our result will be simply:

max(6, s1 + p2 + 1) = max(6, 2 + 18 + 1) = 21

But since we had to reduce our precision by one from 39 to 38, we will also reduce our scale by one from 21 to 20 (again, read that note at the bottom of the MSDN article), therefore the datatype for the result will be decimal(38,20), which is quite different than the decimal(18,2) we started with.


You can avoid issues such as this by using the CAST or CONVERT functions inside of MS SQL Server. You can reference the MSDN article http://msdn.microsoft.com/en-us/library/ms187928.aspx and note the chart in the middle that explains the explicit and implicit conversions actually has an asterisk for the decimal to decimal conversion. They are trying to impress upon you right here that you need to use a CAST or CONVERT function in order to avoid the loss of precision or scale.

So, which one to use? My preference is to use CAST whenever possible, as CONVERT is a function specific to MS SQL Server. That means if you use CAST, your code will be more portable (or, put another way, your code will work against more than one platform). However, CAST cannot be used for formatting purposes, for that you will need to use CONVERT.

In our example, the final code we would want to have is as follows:

DECLARE @first decimal(18,2), @second decimal(18,2)
SET @first = 1234567891234567.87
SET @second = 222.13
SELECT CAST(@first/@second as decimal(18,2)) as 'Result'

This will ensure that we have the desired datatype for our result which is:

Result
5557862023295.22
Next Steps
  • Verify that you are getting the correct datatype returned when performing decimal conversions and use CAST when possible.


Last Update: 9/10/2010


About the author
MSSQLTips author Thomas LaRock
Thomas LaRock is a seasoned IT professional with over a decade of experience. He is a Senior DBA for Confio Software and SQL Server MVP.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, April 25, 2011 - 9:02:15 PM - Jeremy Kadlec Read The Tip

TheSmilingDBA,

I wrote this tip (http://www.mssqltips.com/tip.asp?tip=1589) on rounding some time ago.  I also hope Thomas writes a new tip on rounding, based on this tip and the associated comments.

Thank you,
Jeremy Kadlec


Monday, April 25, 2011 - 9:14:46 AM - TheSmilingDBA Read The Tip

Good tip, Thomas. I had to read the p1, p2, s1, s2 a second time to get p1 meant precision of first variable. Need to brush up on my math.

You might write a follow-up on rounding?

Thanks,

Thomas


Sunday, September 12, 2010 - 8:53:44 PM - ray Read The Tip
This behavior is not unique to MS SQL, to SQL 92 or anyother version of SQL.  At one time all programmers had to worry about whether to choose Integer,  Fixed Point (Numeric, Decimal) or Floating Point (Float, Real) for calculations.  Most compliers had machine specific extensions that faciliated access to the CPUs math functions.

We all had to be aware of the general rules for precesion and scale.  You did not want to be the one who caused overflow, additive cancelation, or any of a number of related problems.

Donald Knuth's "The Art of Computer Programming" is a good reference if you want to uderstand the intracies of Fixed and Floating point math.

This is one of the short comings of modern programmer training.  Everyone just assumes that all math works exactly the same as it does on the calculator or in Excel.


Friday, September 10, 2010 - 1:58:23 PM - SQLRockstar Read The Tip
Michael,

The big picture is that not everyone is aware of the need for explicit datatype definitions when using decimals and MS SQL. It was a topic that came across my desk frequently enough at my old job that I thought it would be worth writing down and submitting as a tip.

When writing the tip I hadn't thought about explicitly defining the result, thanks for pointing that out to me.

Thanks for the comment!


Friday, September 10, 2010 - 12:27:02 PM - Michael Read The Tip
/*

I should mention that there is no need to use the ROUND function as SQL automatically rounds the number when placed into the dynamic decimal value of @result.

To test this use this script;

*/

 

-- Declare all variables

DECLARE @first decimal(18,2) 

DECLARE @second decimal(18,2) 

DECLARE @result decimal(18,2)

-- Set static values

SET @first = 1234567891234567.11 

SET @second = 222.99




 

 

SELECT  @first/@second

SELECT @result = @first/@second

SELECT @result AS 'Result1'

SELECT @result = ROUND ((@first/@second),2)

SELECT @result AS 'Result2'

 


Friday, September 10, 2010 - 11:32:40 AM - Michael Read The Tip
/* would this not be an easier way to script the same result? Or am I missing the big picture? */

-- Declare all variables

DECLARE @first decimal(18,2) 

DECLARE @second decimal(18,2) 

DECLARE @result decimal(18,2)

-- Set static values

SET @first = 1234567891234567.87 

SET @second = 222.13

-- Set dynamic values

SELECT @result = @first/@second 

-- query for dynamic result

SELECT @result AS 'Result'




Friday, September 10, 2010 - 10:44:03 AM - Susan Van Eyck Read The Tip
Thanks for the enlightening explanation! 

This is one of those little things that I wonder about every time I do SQL math.  I usually just tack on a CONVERT and trudge onward.  It's nice to understand what's actually going on.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.