datastage parameter expression , calculate a formated date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 19
- Joined: Wed Sep 08, 2010 6:47 am
datastage parameter expression , calculate a formated date
Hi,
I got a job (named jobA) that have tu put final data in a sequential file.
The name of the file must fit that pattern myFile_DATE1_DATE2.csv
DATE1 represents the today date minus 7 days.
DATE2 represents the today date.
DATE1 and DATE2 must be formated : YYYYMMDD
I may have found a solution :
my jobA has two parameters DATE1 and DATE2.
I create a job sequence where i put my jobA.
In this sequence, i can do an expression to set the value of the two parameters DATE1 and DATE2.
However, it seems that the langage used here is not like OSH (the language used in transformer stages).
Could u help me to write the good code in job parameter expression ?
thanks for ur answers
I got a job (named jobA) that have tu put final data in a sequential file.
The name of the file must fit that pattern myFile_DATE1_DATE2.csv
DATE1 represents the today date minus 7 days.
DATE2 represents the today date.
DATE1 and DATE2 must be formated : YYYYMMDD
I may have found a solution :
my jobA has two parameters DATE1 and DATE2.
I create a job sequence where i put my jobA.
In this sequence, i can do an expression to set the value of the two parameters DATE1 and DATE2.
However, it seems that the langage used here is not like OSH (the language used in transformer stages).
Could u help me to write the good code in job parameter expression ?
thanks for ur answers
blazDelParis,
Welcome to DSXChange. The language used in job sequences is indeed different from that in parallel jobs.
You can compute today's dates using the expression and that of 7 days ago using
p.s. Please try to use correct English instead of SMS-Speak, it is not only easier to read and understand but is much more professional. "U" is not a word and "ur" was an ancient city.
Welcome to DSXChange. The language used in job sequences is indeed different from that in parallel jobs.
You can compute today's dates using the expression
Code: Select all
CONVERT('-','',OCONV(@DATE,'D4-YMD[4,2,2]'))
Code: Select all
CONVERT('-','',OCONV(@DATE-7,'D4-YMD[4,2,2]'))
-
- Participant
- Posts: 19
- Joined: Wed Sep 08, 2010 6:47 am
-
- Participant
- Posts: 19
- Joined: Wed Sep 08, 2010 6:47 am
Hi,
I forgot to tell something, the date to convert , will not be the current date, but an another parameter (let's say paramB).
this parameter is a string formated DD/MM/YYYY.
So, in the above code, I'll need to replace @date by a conversion from my parameter paramB.
paramB is a string , so I need to convert it as a date before applying minus 7.
Could you help me again ?
and by the way, while I'll need to use these functions a lot, I tried to get documentation on how they work , but I can't find it. Would you have a link about that ?
Thanks.
I forgot to tell something, the date to convert , will not be the current date, but an another parameter (let's say paramB).
this parameter is a string formated DD/MM/YYYY.
So, in the above code, I'll need to replace @date by a conversion from my parameter paramB.
Code: Select all
CONVERT('-','',OCONV(@DATE-7,'D4-YMD[4,2,2]'))
Could you help me again ?
and by the way, while I'll need to use these functions a lot, I tried to get documentation on how they work , but I can't find it. Would you have a link about that ?
Thanks.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You may like to use a User Variables activity to break this into simpler expressions and avoid unnecessarily repeated Iconv() function evaluation.
Code: Select all
Convert("-", "", Oconv(Iconv(paramB, "DDMY"), "D-YMD[4,2,2]"))
Code: Select all
Convert("-", "", Oconv(Iconv(paramB, "DDMY") - 7, "D-YMD[4,2,2]"))
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
A good link to learn about date conversion is Date Conversion Demystified on PickWiki.
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.
-
- Participant
- Posts: 19
- Joined: Wed Sep 08, 2010 6:47 am
hi,
I couldn't see the answer containing the code, as I'm not a premium member yet.
Thanks for the link.
I've found a solution , this is :
Iconv giving conversion from string(DD/MM/YYYY) to date.
Oconv giving conversion from date-8 to string(YYYY/MM/DD)
Convert stripping the remaining dashes.
I couldn't see the answer containing the code, as I'm not a premium member yet.
Thanks for the link.
I've found a solution , this is :
Code: Select all
CONVERT( '-', '', OCONV ( ICONV(MY_STRING_DATE, 'D4/DMY[2,2,4]')-8 , 'D-YMD[4,2,2]') )
Oconv giving conversion from date-8 to string(YYYY/MM/DD)
Convert stripping the remaining dashes.
You should have a BASIC manual amongst all of the pdf documentation delivered with the product, they would be explained there. Or in the 'online' help available in the Designer.blazDelParis wrote:and by the way, while I'll need to use these functions a lot, I tried to get documentation on how they work , but I can't find it. Would you have a link about that ?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers