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.
Issue with Timezone in flat files
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 50
- Joined: Mon Jan 21, 2008 9:35 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 50
- Joined: Mon Jan 21, 2008 9:35 am
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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;
3NF: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. So help me Codd.