Why does Ceiling() change the data type to (38,0) here, losing the precision after the decimal point?

This ONLY happens with Decimal(38, X) as shown below.

Running on Microsoft SQL Server 2016

DECLARE @decimal2 DECIMAL(37,4) = 1.1
SELECT COALESCE(1.1,CEILING(@decimal2)) 

^this returns 1.1

DECLARE @decimal DECIMAL(38,4) = 1.1
SELECT COALESCE(1.1,CEILING(@decimal))

^this returns 1!!

3 Answers

3
UnhandledExcepSean On

Ceiling returns an integer, but it should be of the datatype that was passed in. I don't know why this particular issue is caused in the example, but you can overcome it like so:

DECLARE @decimal DECIMAL(38,4) = 1.1
SELECT COALESCE(@decimal,CEILING(@decimal)) --RETURNS 1
SELECT COALESCE(@decimal,CAST(CEILING(@decimal) AS DECIMAL(38,4))) --RETURNS 1.1000

My rule to avoid these types of issues are to always cast different datatypes to a common one.

0
Luis Cazares On

This behavior is (confusely) explained in the Precision, scale, and Length (Transact-SQL) article from Microsoft Docs. This is an extract from the article:

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result.

Now, the confusing part would be to understand how is the precision and scale calculated. From my testing, it seems to be using the addition rules. For precision max(s1, s2) + max(p1-s1, p2-s2) + 1 and for scale max(s1, s2). But since precision would go over 38, the difference is taken from the scale to preserve the integrity of the integral part.

0
ShanksPranks On

This is simply due to the fact that the numeric data type can only hold 38 digits and will fill up the most significant values first so if you say 37,4 it will fill up 37 places to the left of the decimal and keep remaining one to the right. If you put 38 to the left of the decimal there is no room for decimals regardless of what you put on the right.