Date Differenc Math in Transformer

[rxs0005]

hi all

I need to to date math like this

( (Date1 - Date2 ) * 360 ) / 12

I can do this in oracle very easily like below

ROUND((Date1 - Date2 ) / 365 ) * 12

also Date1 > Date2


how do i do this in the transformer using date function provided by datastage.

I think finally what i need is a function that accepts 2 dates and returns an integer number of days

thanks

rxs0005

[kcbland]

Take a look here for good start in writing your own functions:

viewtopic.php?t=85788

[ray.wurlod]

I think finally what i need is a function that accepts 2 dates and returns an integer number of days

DataStage stores dates in internal format as an integer number of days so all you need is simple arithmetic subtraction of the internal forms. Get to internal form using the Iconv() function. The second argument is a string beginning with "D" followed by letters that designate the components' order, for example "DYMD" or "DMDY". Iconv() is clever enough to figure out that everything else is delimiters.

For example

Code: Select all

Iconv(LaterDate, "DYMD") - Iconv(EarlierDate, "DYMD") 

will give you the number of days between the two dates if they are in year-month-day order.

[rxs0005]

Hi

based on the above equation

i did the below

iconv(tpo_loan_orig_out.MATURITY_DATE, "DDMY") - iconv( tpo_loan_orig_out.NOTE_DATE, "DDMY")

both the dates are stored in the DD-MON-YY format eg: 03-AUG-05

the output of this is 0

can some one tell me what am i missing do i have to care for the " - " in the incoming dates.

thanks

R

[chulett]

The 'M' in 'DMY' is for a numeric month. You should be able to try a couple of difference things off the top of my head...

Try "DDMBY2" as 'MB' is the abbreviated month name. Or just a "D2" all by itself, as the default format of 'D' is 'DD-MON-YYYY' and the two at the end tells it a two digit year. That should get you closer.

[rxs0005]

all

Thanks, for now i will just do an Update in the after SQL tab in the Oracle stage this Date airthmetic is too complicated for me to research now i will just use the Oracle date math functions and update the table

thanks for the info

rxs0005

[ray.wurlod]

The 'M' in 'DMY' is for a numeric month. You should be able to try a couple of difference things off the top of my head...

Try "DDMBY2" as 'MB' is the abbreviated month name. Or just a "D2" all by itself, as the default format of 'D' is 'DD-MON-YYYY' and the two at the end tells it a two digit year. That should get you closer.

In Iconv() it duzznt matta - it's a very clever function.

I suspect that, because of the source stage type, R's dates were already in internal format.

[chulett]

Huh... good to know it's so darn smart. I usually stick with the explicit codes.

In any case, the answer that it is just "too complicated" to contemplate is the wrong answer - it's really not and this (meaning date manipulation and math) are fundamental DataStage ETL 'stuffs', something everyone needs to know to play this game. You'd be well served to take the time to learn it.

[ray.wurlod]

Search the Forum for a link to a paper called "Date Conversion Demystified" which is an easy introduction to date conversions.