Hi All,
When i execute the below sql query in oracle database i get the following output:
select FROM_TZ(CAST(TO_DATE(TO_CHAR(sysDate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),
'Europe/London') AT TIME ZONE 'GMT' from dual;
output: 24-MAY-13 01.26.23.000000000 PM GMT
When i run the above query in datastage ORA stage with Varchar(100) as datatype I am getting the below output in my target file:
24-MAY-13 13.29.23.000000 GMT
Can anyone tell me why this PM is getting disappeared?
Because of this above issue when i give this below command in ORA stage:
select
SUBSTR(FROM_TZ(CAST(TO_DATE(TO_CHAR(sysDate,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP),
'Europe/London') AT TIME ZONE 'GMT',11,18) from dual;
output: 13.29.23.000000 GM
Thanks in Advance,
Issue with Timezone date format
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your Oracle date picture ('YYYY-MM-DD HH24:MI:SS') does not specify an AM/PM designator - it specifies a 24 hour clock. Therefore DataStage isn't "disappearing" anything - your other viewer is ADDING the AM/PM designator.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.