fixed point arithmetic for price calculations

I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.

Here are some experiments I did in an SSIS expression editor:

(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"

Of course, what I really want is "39.81", so I went on:

(DT_WSTR, 10) ((DT_DECIMAL, 0) (1.28 * (DT_R8) "31.10")) evaluates to "39"

(DT_WSTR, 10) ((DT_DECIMAL, 1) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

This looks promising! But:

(DT_WSTR, 10) ((DT_DECIMAL, 2) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

(DT_WSTR, 10) ((DT_DECIMAL, 3) (1.28 * (DT_R8) "31.10")) evaluates to "39.808"

Argh... How does one get a floating point value rounded to 2 decimal places?

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?|||

Phil Brammer wrote:

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?

Hmm... That didn't seem to work either.|||Use the ROUND() function. That will work for you.

ROUND(1.28 * 31.1 ,2)|||

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

I didn't think to try ROUND(numeric_expression, length) because the help text in the Expression Builder says that it returns an integer (regardless of the length parameter).

Thanks!

|||

Kevin Rodgers wrote:

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

Hmmm. I had to increase the string length from 10 to avoid a truncation error, so I changed it to 4000 which I think is the maximum for Unicode strings -- no worries. But more testing reveals that (DT_WSTR, 4000) ROUND(numeric_expression, 2) sometimes returns a value with fewer than 2 digits after the decimal point e.g. "16" instead of "16.00" or "133.5" insead of "133.50"'.

Here's what I'm using to ensure that there is a decimal point followed by 2 digits in the result:

FINDSTRING(usd_price, ".", 1) > 0 ? SUBSTRING(usd_price + "00", 1, FINDSTRING(usd_price, ".", 1) + 2) : usd_price + ".00"