Get the fractional part of a decimal with SSIS Expressions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Get the fractional part of a decimal with...

Get the fractional part of a decimal with SSIS Expressions

Rate Topic

Display Mode

Topic Options

Author

Message

rburko

Posted Tuesday, February 19, 2013 7:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:57 AM
Points: 26, Visits: 284

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

Daniel Bowlin

Posted Tuesday, February 19, 2013 8:20 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 2,673, Visits: 2,418

How about
1.2 - FLOOR(1.2)

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

Post #1421670

Phil Parkin

Posted Tuesday, February 19, 2013 8:36 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1421681

rburko

Posted Tuesday, February 19, 2013 9:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:57 AM
Points: 26, Visits: 284

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

rburko

Posted Tuesday, February 19, 2013 9:07 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:57 AM
Points: 26, Visits: 284

Thanks Phil,

In my case, this will always be a positive decimal. But you made a good point.

Post #1421705

« Prev Topic | Next Topic »

Permissions