Decimal Conversions in SQL Server Change Datatype Format

By:   |   Comments (8)   |   Related: 1 | 2 | > Data Types


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.


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:


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" .

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 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 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:

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 6, 2019 - 2:37:36 PM - Kiran Back To Top (81352)

This is regarding converting Exponential value in SQL. I have '1.8547E+11' in my input excel/csv file and when I format this in Excel its showing as 185470383006. But when I convert this using SQL its returning only 185470000000. Is there any way to convert this and get the exact value of the exponential data?

Appreciate your help and reply on this.


SELECT convert(numeric(38,0),cast(1.8547E+11 AS float)) 

Result: 185470000000

Monday, April 25, 2011 - 9:02:15 PM - Jeremy Kadlec Back To Top (13683)


I wrote this tip ( 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 Back To Top (13678)

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?



Sunday, September 12, 2010 - 8:53:44 PM - ray Back To Top (10155)
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 Back To Top (10151)

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 Back To Top (10149)

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 Back To Top (10148)
/* 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 Back To Top (10147)
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.

get free sql tips
agree to terms