Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Get the fractional part of a decimal with SSIS Expressions Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, February 19, 2013 7:40 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, August 26, 2013 1:00 PM Points: 35, Visits: 320
 This shouldn't be too hard but I'm still having trouble getting the fractional part of a decimal number using SSIS expressions without jumping through a lot of hoops. I found this simple method to get the fractional part using T-SQL.`Select 1.2 % 1 `So of course I thought, "SSIS expressions have a modulo operator, I'll just use that." But it doesn't work. It looks like you can only use integers when using the modulo operator in an expression.Short of converting the decimal to a string, finding the decimal position using Findstring, then digging out the fractional part using a substring function, what is the best option for getting the fractional part of a decimal number?
Post #1421649
 Posted Tuesday, February 19, 2013 8:20 AM
 SSCrazy Group: General Forum Members Last Login: Sunday, November 24, 2013 4:07 PM Points: 2,780, Visits: 2,513
 How about1.2 - FLOOR(1.2)Floor returns the largest integer that is less than or equal to the numeric expression
Post #1421670
 Posted Tuesday, February 19, 2013 8:36 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Yesterday @ 11:25 AM Points: 4,632, Visits: 10,568
 Daniel Bowlin (2/19/2013)How about1.2 - FLOOR(1.2)Floor returns the largest integer that is less than or equal to the numeric expressionNot true for negatives. Try this:`select -11.2 - floor(-11.2)`But stick to positive numbers, or use ABS() too and it should work. ____________________________________________________________________________________________Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1421681
 Posted Tuesday, February 19, 2013 9:05 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, August 26, 2013 1:00 PM Points: 35, Visits: 320
 Awesome! That works! I was already using Floor to pull out the integer part of the decimal. It didn't occur to me to use floor to subtract the integer from the decimal to get the fractional part. The only part I had to add was a cast to decimal to get the result; in my case:(DT_DECIMAL,2)((1.2 - FLOOR(1.2)) * 100))Thanks!
Post #1421701
 Posted Tuesday, February 19, 2013 9:07 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, August 26, 2013 1:00 PM Points: 35, Visits: 320
 Thanks Phil,In my case, this will always be a positive decimal. But you made a good point.
Post #1421705

 Permissions