Splitting decimal in sql

Question

Hi guys,

I am getting result as decimal in stored procedure. If I am getting result as 123.45, I want to split it into 123 and 45. Can anybody help?

Answer 1

use SQL function FLOOR() for getting integer part and subtract that from the original for the decimal part

Answer 2

You can also make use of ROUND instead of FLOOR.

See section C. Using ROUND to truncate for trucate, and then subtract that from the original.

Be aware that using FLOOR on negative numbers might not give you the required result.

Have a look at this example

DECLARE @Dec DECIMAL(12,8)

SET @Dec = -123.45

SELECT FLOOR(@DEc)

select round(@Dec, 0, 1)