How to calculate decimal(x, y) max value in SQL Server

Question

How do I know the maximum value of decimal type?

For example:

decimal(5, 2)

Can you please explain the mechanism of decimal type?

Answer 1

You can use an aggregate function in order to get the maximum value in a column

SELECT MAX(myColumn) AS MyColumnMax
FROM myTable

If you want to know the maximum value for each customer for instance, you can group by this customer

SELECT CustomerID, MAX(OrderAmount) AS MaxOrderAmount
FROM CustomerOrders
GROUP BY CustomerID

You can find other aggregate functions here.

---

If, by contrast, you are interested in range of a decimal type, then consider, that you are declaring the total number of digits and decimals. Therefore the maximum number is reached, when all these digits are 9.

So for decimal(5,2) it would be 999.99. 5 is the total number of decimals to the left and to the right of the decimal point. 2 is the number of decimals to the right of the decimal point.

The maximum possible range for decimals is -10^38 + 1 through 10^38 - 1.

Answer 2

The maximum possible value can be calculated using the following maths:

(10 ^ (x-y)) - (10 ^ -y) 

So in your initial example

(10 ^ (5-2)) - (10 ^ -2) = 1000 - 0.01 = 999.99

I'm adding this answer as my google results took me here when trying to remind myself how to calculate the SQL friendly upper bounds of a C# decimal going into an SQL (28,10) decimal field.

999999999999999999.9999999999 by the way.