date conversion plz advice

[bobby]

Hi frnds,
plz advice my date is in format 01-Aug-04(varchar) want to convert it to
08/01/2004(Date) .
Regards,
Bobby

[throbinson]

p_date = passed date
OCONV(ICONV(p_date,"D-DMY[2,3,2]"),"D/MDY[2,2,4]")

[john@dtsisoftware.com]

[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

[chulett]

You need to read the documentation on the OCI plugins that are installed with your client software. There is a section devoted to 'Date Considerations' where issues like this are discussed. In essence:

When you declare something to be of a 'Timestamp' data type in DataStage and then use that in the OCI stage with generated sql, the stage will automatically use the TO_DATE function. The trick is to get the date into the format that matches the mask the TO_DATE function uses so you don't get mismatches like you are seeing. A 'Timestamp' must be in 'YYYY-MM-DD HH24:MI:SS' format. A 'Date' data type is a little trickier as (to me) it looks like it uses the current NLS_DATE format to build the mask. The documentation doesn't really say. However, 'Timestamp' *is* the default data type that the OCI stages expect and what I always use when working with DATE fields in Oracle.

So for your situation, a Timestamp with:

Code: Select all

Oconv(Iconv((Input.TRANS_DT),"D/MDY[2,2,4]"),"D-YMD[4,2,2]") : " 00:00:00"

should work for you if your input date is truly in "MM/DD/YYYY" format.

If you want to try using a 'Date' data type, declare one in the OCI stage and seeing what kind of SQL it generates. On my system, it uses:

Code: Select all

TO_DATE(TheDateField, 'DD-MON-YY'))

I think I'll stick with Timestamps.