datastage parameter expression , calculate a formated date

[blazDelParis]

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

[ArndW]

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

Code: Select all

CONVERT('-','',OCONV(@DATE,'D4-YMD[4,2,2]'))

and that of 7 days ago using

Code: Select all

CONVERT('-','',OCONV(@DATE-7,'D4-YMD[4,2,2]'))

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.

[blazDelParis]

Hello,
thanks you for your answer.
Please, accept my apologies for my poor english as it is not my natural language.

you've been a great help, thanks a lot.

[ArndW]

No worries about English - most of the people here aren't native english speakers (including me), but using SMS-Style words such as "u" "plz" "r" isn't appropriate on a professional and technically oriented site.

[blazDelParis]

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.

Code: Select all

CONVERT('-','',OCONV(@DATE-7,'D4-YMD[4,2,2]'))

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.

[ray.wurlod]

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]"))

[ray.wurlod]

A good link to learn about date conversion is Date Conversion Demystified on PickWiki.

[blazDelParis]

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 :

Code: Select all

CONVERT( '-', '', OCONV ( ICONV(MY_STRING_DATE, 'D4/DMY[2,2,4]')-8 , 'D-YMD[4,2,2]') )

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.

[chulett]

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 ?

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.