Issue with Timezone date format

[HemaV]

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,

[chulett]

"In Oracle database" means what? Toad? I'll wager your tool is converting it from military time for display.

[HemaV]

Sorry for delay in reply.

When i execute the sql query in SQL developer tool its working fine. But when i put the same sql query then PM/AM is getting disappeared..

Thanks in Advance,

[chulett]

Same answer. It's not "fine" there, your tool is converting it for display. DataStage isn't.

[ray.wurlod]

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.