x

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

 (DT_I4)[MyNumericValue]

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:

 (DT_I4)FLOOR([MyNumericValue])

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1231
x11
x9
x6

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