Extract Decimal Part of a Number in SQL Server

To extract decimal part of a number in SQL Server, use the modulo operator with 1 on the absolute value. The remainder is the fraction. One short expression does the job, and a few traps are worth knowing.

Modulo With 1 Returns the Fraction

The modulo operator, written as a percent sign, returns the remainder of a division. Any number divided by 1 leaves only its fraction behind. So 12.87 % 1 returns 0.87. That is the whole technique.

The demo uses a temporary table with five amounts, one of them negative. The query shows four ways to extract decimal part values. Only some of them survive a negative number.

DROP TABLE IF EXISTS #Prices;
CREATE TABLE #Prices (Amount decimal(16,3) NOT NULL);
INSERT INTO #Prices (Amount) VALUES (100.000), (-23.890), (390.077), (12.870), (390.100);
SELECT Amount,
       ABS(Amount) % 1 AS DecimalPart,
       Amount % 1 AS SignedPart,
       Amount - FLOOR(Amount) AS FloorWay,
       Amount - ROUND(Amount, 0, 1) AS TruncateWay
FROM #Prices;

Look at the second row. The modulo of a negative number keeps the sign, so SignedPart is -0.890. Wrap the amount in ABS to get 0.890 when you want the size of the fraction and not its sign. The FLOOR version returns 0.110 for the negative row, because FLOOR rounds toward minus infinity. That answer is wrong for most purposes.

The last column subtracts the truncated value. ROUND with a third argument of 1 cuts the digits instead of rounding them. It returns the signed fraction, the same as SignedPart. Pick ABS with modulo when you want the unsigned fraction, and the truncate version when the sign matters.

Why It Returns 0 for Some Columns

Sometimes an attempt to extract decimal part values returns 0 for every row. The cause is the data type of the column. An int holds no fraction at all. A decimal with a scale of 0 rounds the value on the way in, so 12.87 becomes 13. Both have nothing left after the modulo.

SELECT 12.87 % 1 AS Plain,
       CAST(12.87 AS int) % 1 AS AsInt,
       CAST(12.87 AS decimal(5,0)) % 1 AS NoScale,
       CAST(12.87 AS money) % 1 AS AsMoney;

The money type keeps four decimal places, so its fraction shows as 0.8700. Check the type with sp_help or the column list in Object Explorer. If the fraction is already gone, no query can bring it back. The value has to be stored with a scale, such as decimal(16,3).

The float type has its own problem. The modulo operator doesn’t accept it.

SELECT CAST(12.87 AS float) % 1 AS AsFloat;

Msg 402, Level 16, State 1, Line 1
The data types float and int are incompatible in the modulo operator.

Cast the float to a decimal first, and the expression works.

SELECT CAST(CAST(12.87 AS float) AS decimal(16,3)) % 1 AS FloatFixed;

Digits as a Whole Number

Sometimes you want the digits, such as 87 from 12.87. There are two approaches, and they answer different questions. PARSENAME of the converted text returns every stored digit, including zeros. Multiplying the fraction returns a number, which drops the zeros in front.

SELECT Amount,
       PARSENAME(CONVERT(varchar(30), Amount), 1) AS StoredDigits,
       CAST(ABS(Amount) % 1 * 1000 AS int) AS Thousandths,
       FORMAT(Amount, '0.###') AS Trimmed
FROM #Prices;

The row for 390.077 shows the trap. The text keeps the leading zero, and the number does not. Decide which one you need before you build on it. The Trimmed column shows the amount without its trailing zeros. FORMAT is handy for display and slow over many rows, so avoid it in a large query.

Trailing Zeros Aren’t Stored

A decimal(16,3) column stores 390.100 even when someone typed 390.1. The type keeps a value and a fixed scale. It doesn’t remember how the value was typed, and float doesn’t either. Suppose the business must keep 390.10 apart from 390.1. Store the original text in a second column, or store the number of digits next to the value.

You could argue that string functions make the intent clearer. A CHARINDEX on the point and a SUBSTRING after it read like plain English. They also depend on the text form of the number. A small float converts to scientific notation, and the point disappears.

SELECT CONVERT(varchar(30), CAST(0.00001 AS float)) AS SmallFloat,
       CONVERT(varchar(30), CAST(12.87 AS decimal(16,3))) AS FixedDecimal;

Modulo works on the value itself, so it never meets this problem.

A Practical Use: Minutes From Decimal Hours

Time sheets can store 7.75 hours instead of 7 hours and 45 minutes. The whole hours are the value cast to int, which cuts the fraction. The minutes are the fraction times 60, rounded to a whole number.

SELECT Hours,
       CAST(Hours AS int) AS WholeHours,
       CAST(ROUND(ABS(Hours) % 1 * 60, 0) AS int) AS Minutes
FROM (VALUES (CAST(7.75 AS decimal(5,2))), (CAST(8.33 AS decimal(5,2))), (CAST(0.10 AS decimal(5,2)))) AS t (Hours);

The 8.33 row shows why rounding matters. The fraction 0.33 times 60 is 19.8 minutes, and ROUND makes it 20. Without ROUND, the cast to int would cut it to 19. Pick the rule your payroll uses, and write it down next to the query.

What to Remember

To extract decimal part values, use ABS(value) % 1 for the unsigned fraction. Use value % 1 when the sign matters. Check the column type first, because an int or a decimal with no scale has no fraction to extract. Cast a float to a decimal before you use the modulo operator.

The demo used a temporary table, so nothing stays behind. This line removes it early.

DROP TABLE IF EXISTS #Prices;

A decimal part is not a stored value, it is a remainder you ask for.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.