ORA-01857: not a valid timezone

[atulgoel]

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?

[ray.wurlod]

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

Obviously this is not a DataStage problem.

[chulett]

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

[qt_ky]

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