Date Differenc Math in Transformer
Moderators: chulett, rschirm, roy
Date Differenc Math in Transformer
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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 examplewill give you the number of days between the two dates if they are in year-month-day order.
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")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
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.![Wink :wink:](./images/smilies/icon_wink.gif)
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In Iconv() it duzznt matta - it's a very clever function.chulett wrote: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.
I suspect that, because of the source stage type, R's dates were already in internal format.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: