Hi frnds,
plz advice my date is in format 01-Aug-04(varchar) want to convert it to
08/01/2004(Date) .
Regards,
Bobby
date conversion plz advice
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
-
- Participant
- Posts: 6
- Joined: Mon Dec 27, 2004 3:32 pm
[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
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
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:
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:
I think I'll stick with Timestamps.
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"
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'))
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers