Separate Float into Integer and Fractional Parts

I need to separate a floating point decimal into 2 integers, one that represents the whole number part, and one that represents the factional part.  For example, if the float is 10.25, I need to get 10 and 25.

The first part is easy, CONVERT(INT, [floating number])

However, I can't figure out the fractional part because there can be any number of digits after the decimal.

Both [floating number]%(CONVERT(INT, [floating number])) and [floating number] - (CONVERT(INT, [floating number])) will remove the whole number part, leaving me with 0.25 in my example.  My challenge is scaling 0.25 up to 25, without knowing that there are only 2 digits after the decimal.

I hope that's not too repetitive.  Any help would be much appreciated.  Thanks.
DBag1Asked:
Who is Participating?
 
deightonprogCommented:
--works for -ve numbers

DECLARE @VAL as float;
SET @VAL = -27.3344333;
--SET @VAL = 3;

DECLARE @SGN AS INTEGER;
SET @SGN = 1;
IF @VAL<0 
 SELECT @SGN = -1, @VAL = 0 - @VAL;

DECLARE @DEC AS varchar(50);
SET @DEC = CAST((@VAL - FLOOR(@VAL)) AS VARCHAR);
IF CHARINDEX('.', @DEC) > 0
  SET @DEC = SUBSTRING(@DEC, CHARINDEX('.',@DEC) + 1, LEN(@DEC) - CHARINDEX(@DEC,'.')); 



SELECT @SGN * CAST(FLOOR(@VAL) AS INTEGER) AS THEINT, @DEC THEREST

Open in new window

0
 
deightonprogCommented:
for positive numbers, including whole numbers

--works for positive numbers only, for negative would need some thought as to
-- how it should work

DECLARE @VAL as float;
SET @VAL = 3.14159265;
--SET @VAL = 3;
DECLARE @DEC AS varchar(50);
SET @DEC = CAST((@VAL - FLOOR(@VAL)) AS VARCHAR);
IF CHARINDEX('.', @DEC) > 0
  SET @DEC = SUBSTRING(@DEC, CHARINDEX('.',@DEC) + 1, LEN(@DEC) - CHARINDEX(@DEC,'.')); 



SELECT CAST(FLOOR(@VAL) AS INTEGER) AS THEINT, @DEC THEREST

Open in new window

0
 
tigin44Commented:
DECLARE @N1  int
DECLARE @N2  int

SELECT @N1 = ROUND([floating number],0)
SELECT @N2 = ROUND([floating number] - @N1, 2)

0
 
DBag1Author Commented:
Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.