SQL Server: fractional part of number

Unfortunately, SQL Server doesn't have a function like frac to extract the fractional part of number. The method is to subtract the integer part from the origin value.

DECLARE @n1 float, @n2 float, @n3 float;
SELECT @n1 = 123.456, @n2 = 234.567, @n3 = 456.789;
SELECT @n1 - round(@n1, 0, 1) AS frac_n1,
       @n2 - round(@n2, 0, 1) AS frac_n2,
       @n3 - round(@n3, 0, 1) AS frac_n3

Results (not so accurate with the float type)

frac_n1                frac_n2                frac_n3
---------------------- ---------------------- ----------------------
0,456000000000003      0,567000000000007      0,788999999999987

For more accuracy use decimal or money type instead

DECLARE @n1 numeric(10,5), @n2  numeric(10,5), @n3  numeric(10,5);
SELECT @n1 = 123.456, @n2 = 234.567, @n3 = 456.789;
SELECT @n1 - round(@n1, 0, 1) AS frac_n1,
       @n2 - round(@n2, 0, 1) AS frac_n2,
       @n3 - round(@n3, 0, 1) AS frac_n3

frac_n1    frac_n2    frac_n3
---------  ---------  ---------
0.45600    0.56700    0.78900

I highly recommend to use the round function in the truncate mode rather the simple casting to integer type because a number overflow error may occur.

DECLARE @n1 float;
SELECT @n1 = 123.456E25;
SELECT @n1 - round(@n1, 0, 1) AS frac_n1; -- OK
SELECT @n1 - CAST(@n1 AS int) AS frac_n1; -- will raise error

frac_n1
----------------------
0

frac_n1
----------------------
Msg 232, Level 16, State 3, Line 18
Arithmetic overflow error for type int, value = 1234560000000000000000000000.000000.

Mechanics of software Programming is thinking, not typing
Home \| Categories \| Tags \| Search	\| eng \| рус