November 21, 2013 at 12:14 pm
For following date im using
10/15/2013 10:17:26 am
Data going in Oracle:
(DT_STR,75,1252)((SUBSTRING(Date,1,4) + "-" + SUBSTRING(Date,5,2) + "-" + SUBSTRING(Date,7,2) + " " + SUBSTRING(Date,9,11) + ":" + SUBSTRING(Date,11,13) + ":" + SUBSTRING(Date,13,15)))
Getting the error:
[Oracle Destination [1867]] Error: OCI error encountered. ORA-01850: hour must be between 0 and 23
Shaun
November 21, 2013 at 12:52 pm
Shaun2012 (11/21/2013)
For following date im using10/15/2013 10:17:26 am
Data going in Oracle:
(DT_STR,75,1252)((SUBSTRING(Date,1,4) + "-" + SUBSTRING(Date,5,2) + "-" + SUBSTRING(Date,7,2) + " " + SUBSTRING(Date,9,11) + ":" + SUBSTRING(Date,11,13) + ":" + SUBSTRING(Date,13,15)))
Getting the error:
[Oracle Destination [1867]] Error: OCI error encountered. ORA-01850: hour must be between 0 and 23
Shaun
Did you get this figured out? If I use the string you indicate above it will be parsed like so.
"10/15-20-3 10:17:26 " which would make no sense to the destination.
Is the source datatype a string or Datetime?
If you cast this as a Datetime instead of a string wouldn't it work?
November 21, 2013 at 12:59 pm
it is timestamp(6)
i changed the formula
DT_STR,75,1252)((SUBSTRING(Date,1,2) + "-" + SUBSTRING(Date,4,2) + "-" + SUBSTRING(Date,7,4) + " " + SUBSTRING(Date,12,8)))
but now i get invalid month error
[Oracle Destination [3138]] Error: OCI error encountered. ORA-01843: not a valid month
November 21, 2013 at 2:02 pm
Have you validated that all the date values in the file follow this same format?, and are you sure oracle is accepting the dd/mm/yyyy format, and is not expecting mm/dd/yyyy?
I think you need to change your expression so it creates a string so its in this format.
YYYY-MM-DD HH24:MI:SS
November 22, 2013 at 7:18 am
how to create string in this format in the derived column
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply