SQL server varchar to Oracle date format

[Victoria]

Hi,
I have to read from SQL Server database a string which has a format 'MM/DD/YYYY HH:MI:SS PM', and save them as Date datatype in Oracle. How do I do that? I got errors when I tried the following:
1. SQL Server as Varchar - Oracle as Timestamp and used TO_DATE(:138, 'MM/DD/YYYY HH:MI:SS PM') during insertion
2. SQL Server as Timestamp - Oracle as Timestamp and used TO_DATE(:138, 'MM/DD/YYYY HH:MI:SS PM') during insertion
Thanks..

[ArndW]

Which part gave you errors - the reading from SQL server or the writing to Oracle? What was the error?

[Victoria]

One error is:
Abnormal termination of stage VRV_ORDERS_DRS..Transformer_24
detected

Or sometimes:
ORDERS_STG..Transformer_24: ORA-01830: date format picture ends before converting entire input string
-- probably because the SQL Server format is 'YYYY-MM-DD 00:00:00.000'
and the Oracle tries to convert using TO_DATE(:138, 'YYYY-MM-DD HH24:MI:SS')

[ray.wurlod]

Yes, that seems likely. You need to make sure that column #138 contains all the characters needed by the target database.

[chulett]

For the second, you need to transform the SQL Server timestamp to conform to what Oracle is expecting, that or use your own sql with a matching 'date format picture'.