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?
ORA-01857: not a valid timezone
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: