DSXchange

Hi,

I have the below source query which is running fine in Oracle but while running in Datastage its giving an error "ORA-01857: not a valid timezone"
Also I am able to view the data in Datastage.

SELECT CNTRCT_ITEM_KEY,
cast(to_char ((new_time(SOURCE_CREATE_DATE,'GMT',to_char(from_tz(CAST(TO_DATE(substr(TO_char(SOURCE_CREATE_DATE, 'YYYYMMDDHH24MISS'),1,8), 'YYYYMMDD') AS TIMESTAMP),'US/Pacific') ,'TZD'))),'J') as varchar2(75)) as
PST_SOURCE_CREATE_DATE,
cast(to_char ((new_time(CNTRCT_ITEM_END_DATE,'GMT',to_char(from_tz(CAST(TO_DATE(substr(TO_char(CNTRCT_ITEM_END_DATE, 'YYYYMMDDHH24MISS'),1,8), 'YYYYMMDD') AS TIMESTAMP),'US/Pacific') ,'TZD'))),'J') as varchar2(75)) as
PST_CNTRCT_ITEM_END_DATE,
cast(to_char ((new_time(CNTRCT_ITEM_START_DATE,'GMT',to_char(from_tz(CAST(TO_DATE(substr(TO_char(CNTRCT_ITEM_START_DATE, 'YYYYMMDDHH24MISS'),1,8), 'YYYYMMDD') AS TIMESTAMP),'US/Pacific') ,'TZD'))),'J') as varchar2(75)) as
PST_CNTRCT_ITEM_START_DATE,
cast(to_char ((new_time(CNTRCT_ITEM_EXTENDED_DATE,'GMT',to_char(from_tz(CAST(TO_DATE(substr(TO_char(CNTRCT_ITEM_EXTENDED_DATE, 'YYYYMMDDHH24MISS'),1,8), 'YYYYMMDD') AS TIMESTAMP),'US/Pacific') ,'TZD'))),'J') as varchar2(75)) as
PST_CNTRCT_ITEM_EXTENDED_DATE
FROM DIMS.SERVICE_CONTRACT_ITEM


Please suggest what should I do?

Give something other that "TZD" as the timezone designator?

Obviously this is not a DataStage problem.

You may need an 'alter session' Before SQL to ensure that the session timezone is set correctly vs. the timezone on the db server.

Did you test the query from the Oracle Client that's on the DataStage server vs. the client on your PC?