SSIS Derived Column: Round or Floor

I ran into something perculiar in SSIS today.
In a Derived Column package I was trying to truncate a numeric value to an integer, like this


It turns out that this does not do a truncate, but it rounds (up or down) to the nearest integer.
So I had to do this to get it to truncate:


I tried searching for an answer, but was not able to find any.
Now there is an explaination to be found.

more ▼

asked Jun 30, 2011 at 12:39 AM in Default

avatar image

Henrik Staun Poulsen
589 14 17 20

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Only to be completely correct to really truncate (cut off decimal places) from signed numeric value you have to write following expression:

 SIGN([MyNumericValue]) * FLOOR(ABS([MyNumericValue]))

As single floor function returns the largest integer value, that is less then or equal to the value provided.

In case of negative value eg. FLOOR(-1.34) will return -2.

more ▼

answered Jun 30, 2011 at 03:24 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

Very important point, not to be missed. (This time I only had positive numbers, so I do not have to change anything in my code).

But I think it is really naughty of SSIS to do a round. I still read the code as a Truncate, but it is really doing a Cast(round(x,0) as integer). To my immense surprise

Jun 30, 2011 at 04:19 AM Henrik Staun Poulsen
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 30, 2011 at 12:39 AM

Seen: 3475 times

Last Updated: Jun 30, 2011 at 12:39 AM

Copyright 2018 Redgate Software. Privacy Policy