Page 1 of 1

TIMESTAMP(6) WITH LOCAL TIME ZONE datatype

Posted: Mon Feb 25, 2008 8:47 am
by anandsh16
Hi

I am extarcting data from a table in oracle. One of the column is having a datatype as TIMESTAMP(6) WITH LOCAL TIME ZONE. When i query the oracle at sqlplus promt i get the column value as

select Open_date from open_tckt where ref_num = 'IN26'
2 /

OPEN_DATE
---------------------------------------------------------------------------
29-SEP-07 05.45.45.000000 AM
for this column i have given the column datatype as timestamp in datastage. I have used the oracle enterprise stage for extraction.

But when i view the data in datastage , the same value appears to be 6 hours ahead i.e. 2007-09-29 11:45:45 AM

I have executed the below command at sqlplus prompt. The timezone is GMT - 6. --------------------------------------------------------------------------------
select to_char(sysTIMESTAMP,'HH24:MI:SS.FF TZH:TZM') from dual;

TO_CHAR(SYSTIMESTAMP,'HH2
-------------------------
07:49:10.657667 -06:00
--------------------------------------------------------------------------------

So datastage is presenting the date in GMT instead of shwoing me in GMT-6

Any ideas how to show the time in original timezone in oracle instead of GMT timezone ?