Difference of @value and FLOOR(@value) is 0 for DECIMAL type

Question

A lot of answers here say use

SELECT @value - FLOOR(@value)

to get the decimal part of a number. See here or here for examples.

I'm getting what I consider to be weird behavior when I do this.

DECLARE @test DECIMAL(38, 8)
SET @test = 123.05468800

SELECT @test - FLOOR(@test)

Result: 0

I can do

SELECT 123.05468800 - FLOOR(123.05468800)

Result: 0.05468800

and I can change the type of @test to FLOAT and it works. Also FLOOR(@test) returns 123 as expected.

I imagine this has something to with precision of the decimal type, but the only MSDN page I could find on the subject is really sparse.

So what's going on? Why don't I get the decimal portion .05468800? And what should I be doing or using to get this?

Answer 1

DECLARE @test DECIMAL(38, 8), @test2 DECIMAL(28, 8)
SET @test = 123.05468800
SET @test2 = 123.05468800

SELECT 
    @test as test, 
    FLOOR(@test) AS floortest, 
    @test-FLOOR(@test) AS broken, 
    @test - CAST(FLOOR(@test) AS DECIMAL(38, 8)) AS fixed
INTO gbntest;

SELECT 
    @test2 as test, 
    FLOOR(@test2) AS floortest, 
    @test-FLOOR(@test2) AS working
INTO gbntest2;

SELECT 
    123.05468800 as test,
    FLOOR(123.05468800) as floortest,
    123.05468800 - FLOOR(123.05468800) as working
INTO gbntest3;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.TABLE_NAME LIKE 'gbntest%';
DROP TABLE gbntest;
DROP TABLE gbntest2;
DROP TABLE gbntest3;

Note the middle 2 for gbntest are decimal (38,0)

However, with constants or decimal (28,8) it works. So does (29,8) and (30,8)

But then with (31,8) you get (38,7) back.

The MSDN "Precision, Scale, and Length" describes why

                precision                                 scale
 ...
 e1 - e2        max(s1, s2) + max(p1-s1, p2-s2) + 1       max(s1, s2)

For (31,8), you get a precision of (40, 8)

max(8,0) + max(31-8, 31-0) + 1 -> 8 + 31 + 1 -> 40
max(8,0) -> 8

(40,8) should be adjusted down to (38,6). So I've bollixed my calculation somewhere :-) but I hope you get the idea...