[quote="throbinson"]p_date = passed date
OCONV(ICONV(p_date,"D-DMY[2,3,2]"),"D/MDY[2,2,4]")[/quote]
Greetings, throbinson
I would appreciate your feedback on a similar situation I encountered to the issue from Bobby. I thank you in advance for any advice you may provide.
This should be simple, but I have spent three days on it with no success. I have read through dozens of posts in the DSXchange and through the docs...I figured it was time to ask someone who knew a lot more than I...
I was very interested in your reply, because in my situation...I have a text file, and I was initally successful when I eft the date in a varchar string format until I got to the Oracle database stage, then I initially used a user-defined sql conversion as follows :
insert TO_DATE(Trans_dt)
and that worked fine.
However, my requirements changed, and I can no longer user a user-defined sql statement...I must use the sql insert statement generated by DataStage. So, here is my dilemma...
Ever since then, I have had no luck. Either I get the message
"an invalid ORA-01843: not a valid month"
or "cannot insert NULL"
I can read the data in the sequential data stage file, but past the transformer, no matter what datatype I use (varchar- string or date-date), I get errors.
I tried the
Oconv(Iconv((Input.TRANS_DT),"D/MDY[2,2,4]"),"D/MDY[2,2,4]")
in the derivation, no luck.
my job is
sequential -> transformer -> oracle
I even tried the TO_DATE format in the derivation, but apparently DataStage doesn't recognize TO_DATE in the derivation, but also uses TO_DATE automatically....but it doesn't seem to work.
Do you have any ideas where I am missing the boat?
thanks again
john