how & where to find @DATE variable in datastage

mandyli · Post by **mandyli** » Mon Apr 14, 2014 9:08 am

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

chulett · Post by **chulett** » Mon Apr 14, 2014 9:19 am

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

mandyli · Post by **mandyli** » Mon Apr 14, 2014 9:45 am

Thanks Lot.

Sorry

where to define the @DATE values and format?

Thanks
Man

mandyli · Post by **mandyli** » Mon Apr 14, 2014 9:50 am

Sorry

how to access and get and change the format of System Variables?

@DATE

Thanks
Man

chulett · Post by **chulett** » Mon Apr 14, 2014 9:51 am

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.

mandyli · Post by **mandyli** » Mon Apr 14, 2014 10:48 am

Sorry I am not seeing your full information.
I lost my premium memebrship.

chulett · Post by **chulett** » Mon Apr 14, 2014 10:55 am

You really need to consider finding it again.

mandyli · Post by **mandyli** » Mon Apr 14, 2014 10:55 am

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

ray.wurlod · Post by **ray.wurlod** » Mon Apr 14, 2014 3:48 pm

Right click on the expression and choose Validate Expression. The expression editor will give you a diagnosis.

mandyli · Post by **mandyli** » Tue Apr 15, 2014 2:05 pm

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