Only display the values to the right of a Decimal Number

Question

Hi there,

I use SQL Server 2005 and need to test whether values in a column that's metadata has been specified as DECIMAL(18.3) actually contains data that has values to the right of the Decimal point, and if so, what these values are.

I've read a few articles that only discuss how to drop off the decimal places or how to round the values, but not how to ONLY display what is stored to the right of the decimal point.

Your help would be greatly appreciated.

Kind Regards,

Ignacio.

Answer 1

Try:

SELECT a - FLOOR(a)
FROM ...

Answer 2

SELECT decimalnumber - FLOOR(decimalnumber) AS decimalpart
FROM mytable
WHERE decimalnumber - FLOOR(decimalnumber) > 0

Answer 3

This may not always work the way you expect it to. The problem occurs when you have negative numbers. You can think of FLOOR as a type of rounding, where it always rounds down to the next whole number. Floor(3.14) = 3, and Floor(-3.14) = -4.

To get the value of a number after the decimal point, you can use the ParseName function, which will work for positive and negative numbers.

Select ParseName(-3.9876, 1)
Select ParseName(-3.1234, 1)
Select ParseName(3.9876, 1)
Select ParseName(3.1234, 1)