Issue with Timezone in flat files

hemaarvind1 · Post by **hemaarvind1** » Mon May 09, 2016 4:56 am

Hi All,

Could you please help me in providing your solution for the below scenario:

We are using Oracle table as source and we have the date format coming from the source as below.
01-JAN-00 12.00.01.000000000 AM -5:00

We are trying to load the date format as it is including the timezone into a flat file. We are using a sequential file stage for this.

While doing this, we are having the timestamp format loaded ,however, the timezone part "-5"00" is getting truncated and we are not able to load it.

However,when we try to use a dataset in a target or any other oracle table, we are able to get the timestamp as it is as expected.

Could you please suggest how can we retrieve the time zone information.
FYI, we have used different flat file formats (.dat,.csv..xls etc) and also tried different timestamp formats in the sequential file stage options,but invain.

Besides, FYI, we have the NLS option disabled for the datastage project.

Please suggest.

chulett · Post by **chulett** » Mon May 09, 2016 6:32 am

Why not just target a string in the flat file?

ray.wurlod · Post by **ray.wurlod** » Mon May 09, 2016 5:12 pm

You can probably retrieve the system's timezone from the TZ environment variable.

hemaarvind1 · Post by **hemaarvind1** » Mon May 09, 2016 9:58 pm

Chulett and Ray,

Thank You for your inputs.

The Job which we are using is generic and we are using RCP. Hence,we don't have specific metadata in the jobs to convert the timestamp to a String at job level.

Besides, to Answer Ray, We tried using the TZ environment variable,however, we didn't get the expected output.

chulett · Post by **chulett** » Tue May 10, 2016 6:50 am

Ah... RCP. That would have been good information to include in your first post.

ray.wurlod · Post by **ray.wurlod** » Tue May 10, 2016 5:34 pm

Is TZ being set for your project/environment?

battaliou · Post by **battaliou** » Wed May 11, 2016 9:25 am

Generic and RCP? That's novel. I guess you are able to affect your oracle select statement? If so, you could always convert your Timestamp upfront using the to_char function. e.g. select to_char(TIMESTAMP '1999-01-15 8:00:00 -8:00') as TZ from dual‏;