Page 1 of 1
how & where to find @DATE variable in datastage
Posted: Mon Apr 14, 2014 9:08 am
by mandyli
Hi
I am doing migration job from 8.5 to 9.1 .
I am seeing different date format in 9.1
in 8.5 I am getting run date : yyyymmdd
Code: Select all
If RUNDATE='1000-01-01' then Ereplace (oconv(@DATE-1,"DYMD[4,2,2]")," ","") else Ereplace(RUNDATE,"-","")
and 9.1 I am getting yyyy/mm/dd
where to set or change the DATE values ?
@DATE.
Thanks
Man
Posted: Mon Apr 14, 2014 9:19 am
by chulett
You did not specify a date separator in the OConv function, which is
not the same as telling it you are wanting a space. Explicitly declare it so you can explicitly remove it:
Code: Select all
If RUNDATE='1000-01-01' then Ereplace (oconv(@DATE-1,"D/YMD[4,2,2]"),"/","") else Ereplace(RUNDATE,"-","")
Posted: Mon Apr 14, 2014 9:45 am
by mandyli
Thanks Lot.
Sorry
where to define the @DATE values and format?
Thanks
Man
Posted: Mon Apr 14, 2014 9:50 am
by mandyli
Sorry
how to access and get and change the format of System Variables?
@DATE
Thanks
Man
Posted: Mon Apr 14, 2014 9:51 am
by chulett
There's nothing to 'define/change' about it as @DATE returns the current date in internal format. It's all about how you convert it to an external format and I've already pointed out your issue there.
Posted: Mon Apr 14, 2014 10:48 am
by mandyli
Sorry I am not seeing your full information.
I lost my premium memebrship.
Posted: Mon Apr 14, 2014 10:55 am
by chulett
You really need to consider finding it again.
Posted: Mon Apr 14, 2014 10:55 am
by mandyli
And also I am testing with following in 9.1 Transformer stage. but it is in Red color. It looks like some syntax issue.
Ereplace (oconv(CurrentDate()-1,"DYMD[4,2,2]")," ","")
Ereplace (oconv(@DATE-1,"DYMD[4,2,2]")," ","")
Thanks
Man
Posted: Mon Apr 14, 2014 3:48 pm
by ray.wurlod
Right click on the expression and choose Validate Expression. The expression editor will give you a diagnosis.
Posted: Tue Apr 15, 2014 2:05 pm
by mandyli
Hi Ray,
I am getting oconv variable not defined error. Any way I have fixed this issue. I have disabling the Time/Date option from NLS tab at job and job sequence level.
//-----------------------------------------------
As noted in the documentation section "Correlative and Conversion Codes" NLS environments treat some functions differently such as Oconv and Iconv. To disable the NLS conversion setting to achieve the date without the slashes the following steps should be taken. by disabling the Time/Date option slashes will not be inserted in the field being processed.
1. Open Job/Sequence Properties
2. Select the NLS Tab
3. Under Default Locale categories set Time/Date to OFF.
//------------------------------------------------------------------------
Please refer IBM tech note.
DataStage functions OCONVand ICONV work differently at Information Server 8.x.
http://www-01.ibm.com/support/docview.w ... wg21416670
I am not sure why the IBM is not fixed this issue on Datastage 9.1 version and 9.1 fix pack.
Thanks
Man