Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get the fractional part of a decimal with SSIS Expressions Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 7:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
How about
1.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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 5,174, Visits: 12,028
Daniel Bowlin (2/19/2013)
How about
1.2 - FLOOR(1.2)

Floor returns the largest integer that is less than or equal to the numeric expression


Not 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
Thanks Phil,

In my case, this will always be a positive decimal. But you made a good point.
Post #1421705
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse