Splitting decimal in sql

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?

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

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) 

try this;

DECLARE @result DECIMAL(8,2) = 123.45

SELECT CAST(round(@result,0) AS FLOAT)
SELECT REPLACE(@result % 1 ,'0.','') 

OR

DECLARE @result decimal(8,2) = 123.45
select PARSENAME(@result, 2) AS LeftSideValue, PARSENAME(@result, 1) AS RightSideValue