SQL Server Varchar Date to Oracle Timestamp conversion

Childrensmn · Post by **Childrensmn** » Mon Mar 30, 2015 11:56 am

I have spent considerable time trying to fix that but get error message.
I have Claim_St_Dt coming as Varchar from SQL Server (Here is how the date comes as "08/22/2013". Just date no time.
I want to insert this into Oracle table with Timestamp datatype.
50% of the rows are NULL for this column.

I tried "StringToTimestamp", "StringToTimestamp : " 00:00:00", OConv/Iconv in the transformer but nothing seems to be working.

Here is the error in the log file:
"APT_CombinedOperatorController,0: Data string '08/22/2013' does not match format '%yyyy %mm %dd %hh %nn %ss': the value for tag %yyyy has fewer characters than expected."

What am I doing wrong? In other job, I have a similar datatype coming out of SQL Server (same format: 08/22/2013) and StringToTimestamp works fine. Not in this job though..

Thanks for your help.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 30, 2015 3:34 pm

Your default format string for timestamp expects YMD order but you are providing MDY order. That's basically what you're doing wrong.

priyadarshikunal · Post by **priyadarshikunal** » Tue Mar 31, 2015 12:53 am

also I think you need to mention "/" as well. Something like below

Code: Select all

StringToTimeStamp(Claim_St_Dt:" 00:00:00","%mm/%dd/%yyyy %hh:%nn:%ss")

Childrensmn · Post by **Childrensmn** » Fri Apr 03, 2015 1:54 pm

This worked:

StringToTimestamp(CLAIM_ST_DT, "%mm/%dd/%yyyy") : " 00:00:00"