DECIMAL

The DECIMAL type is used to represent numbers with a specific maximum precision and fixed decimal places. This data type is very useful in scenarios that require precise numerical calculations, such as finance, accounting, or other fields, because it can avoid rounding errors associated with floating-point numbers.

Syntax

DECIMAL(precision, scale)

• precision: Represents the total number of digits, including digits on both sides of the decimal point. The value range is from 1 to 38.
• scale: Represents the number of digits after the decimal point. The value range is from 0 to precision, and it cannot be greater than precision.

Example

SELECT CAST(1234.56 AS DECIMAL(10, 2)); -- Result is 1234.56
SELECT CAST(123.456 AS DECIMAL(5, 3));    -- Result is null
SELECT CAST(1.23 AS DECIMAL(4, 2));      -- Result is 1.23
SELECT CAST(1234 AS DECIMAL(6, 2));      -- Result is 1234.00
SELECT CAST(0.1234 AS DECIMAL(5, 4));    -- Result is 0.1234

User Guide

• When scale is 0, it means the value is an integer.
• When the values of precision and scale are the same, it means the value is a pure decimal.
• When performing numerical conversions, if the conversion result exceeds the range of the DECIMAL type, data truncation or rounding may occur.
• When comparing DECIMAL type values, their precision and scale should be considered to avoid comparison errors due to rounding errors.

Notes

• When using the DECIMAL type, the values of precision and scale should be reasonably chosen according to actual needs to ensure the accuracy of the values and calculations.