SQLServerCentral » SQL Server 2005 » SQL Server 2005 Integration Services » Get the fractional part of a decimal with SSIS ExpressionsInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralWed, 26 Jul 2017 07:14:51 GMT20Get the fractional part of a decimal with SSIS Expressionshttps://www.sqlservercentral.com/Forums/FindPost1421649.aspxThis 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.
[code="sql"]
Select 1.2 % 1
[/code]
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?Tue, 19 Feb 2013 09:07:03 GMTrburkoRE: Get the fractional part of a decimal with SSIS Expressionshttps://www.sqlservercentral.com/Forums/FindPost1421705.aspxThanks Phil,
In my case, this will always be a positive decimal. But you made a good point.Tue, 19 Feb 2013 09:07:03 GMTrburkoRE: Get the fractional part of a decimal with SSIS Expressionshttps://www.sqlservercentral.com/Forums/FindPost1421701.aspxAwesome! 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!Tue, 19 Feb 2013 09:05:54 GMTrburkoRE: Get the fractional part of a decimal with SSIS Expressionshttps://www.sqlservercentral.com/Forums/FindPost1421681.aspx[quote][b]Daniel Bowlin (2/19/2013)[/b][hr]How about
1.2 - FLOOR(1.2)
Floor returns the largest integer that is less than or equal to the numeric expression[/quote]
Not true for negatives. Try this:
[code="sql"]select -11.2 - floor(-11.2)[/code]
But stick to positive numbers, or use ABS() too and it should work.Tue, 19 Feb 2013 08:36:17 GMTPhil ParkinRE: Get the fractional part of a decimal with SSIS Expressionshttps://www.sqlservercentral.com/Forums/FindPost1421670.aspxHow about
1.2 - FLOOR(1.2)
Floor returns the largest integer that is less than or equal to the numeric expressionTue, 19 Feb 2013 08:20:53 GMTDaniel Bowlin