text file to oracle date
Moderators: chulett, rschirm, roy
text file to oracle date
Hi ,
I have simple job load data from text file to oracle table.
The dates are coming in following format : 9/16/1989
when I am trying to load into oracle its loading nothing (null) , i tried using iconv , oconv but no use ,
Can you please explain what i have to use , I tried to read iconv and oconv explaination , but if the data is coming into date format in text file do we have to use iconv or oconv ....
Please suggest ...
I have simple job load data from text file to oracle table.
The dates are coming in following format : 9/16/1989
when I am trying to load into oracle its loading nothing (null) , i tried using iconv , oconv but no use ,
Can you please explain what i have to use , I tried to read iconv and oconv explaination , but if the data is coming into date format in text file do we have to use iconv or oconv ....
Please suggest ...
You should use IConv/OConv to convert that external format to YYYY-MM-DD, then append a zero time and use a Timestamp data type. The generated SQL will include the appropriate TO_DATE() function to get it into your DATE field all nice and proper like.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Sorry, but it can't really be "working fine" as Oracle cannot process a DataStage internal date format. However, if the IConv fails, the value will pass through unchanged and so that's probably what is making it look like it is working for you.
You really should have a standard for Oracle dates that doesn't rely on a particular NLS_DATE value. I always convert incoming values to an ISO timestamp in YYYY-MM-DD HH24:MI:SS format and add a zero time for dates that "don't need" a time portion and ensure the SQL has a TO_DATE() wrapper for that column with a matching mask. Works 100% of the time and we can revisit this when you figure out what you're doing isn't quite kosher.![Wink :wink:](./images/smilies/icon_wink.gif)
You really should have a standard for Oracle dates that doesn't rely on a particular NLS_DATE value. I always convert incoming values to an ISO timestamp in YYYY-MM-DD HH24:MI:SS format and add a zero time for dates that "don't need" a time portion and ensure the SQL has a TO_DATE() wrapper for that column with a matching mask. Works 100% of the time and we can revisit this when you figure out what you're doing isn't quite kosher.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you are using an ODBC stage, then it can accept and properly convert a DataStage internal format date. If you are using an Oracle stage, then you need to present a string in correct timestamp format ("correct" = matching the Oracle DATE picture).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
We did not have any information about which stage types were being used, so I thought I'd throw in that gem. It also depends on whether Data Element "Date" (etc.) is specified whether internal format conversion occurs.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Hi ,
The date is coming in the text file in this format: 2/4/2002
I am convertinbg it into transformer as following :
If IsNull(trim(tdAdmission_From.ReferralDate)) then @null else oconv(iconv(trim(tdAdmission_From.ReferralDate),"D/MDY[2,2,4]"),"D/MDY[2,2,4]"):' ':'00:00:00'
Then standard OCI date format TO_DATE(:20, 'YYYY-MM-DD HH24:MI:SS') to load into target table ...
But it is loading nulls...rejecting rows...ORA-01843: not a valid month
pleASE SUGGEST...
The date is coming in the text file in this format: 2/4/2002
I am convertinbg it into transformer as following :
If IsNull(trim(tdAdmission_From.ReferralDate)) then @null else oconv(iconv(trim(tdAdmission_From.ReferralDate),"D/MDY[2,2,4]"),"D/MDY[2,2,4]"):' ':'00:00:00'
Then standard OCI date format TO_DATE(:20, 'YYYY-MM-DD HH24:MI:SS') to load into target table ...
But it is loading nulls...rejecting rows...ORA-01843: not a valid month
pleASE SUGGEST...
Both format masks are wrong. Try:
Code: Select all
OConv(IConv(Trim(tdAdmission_From.ReferralDate),"D"),"D/YMD[4,2,2]"):' 00:00:00'
Last edited by chulett on Mon Aug 23, 2010 1:15 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks a lot Craig,
I really appreciate ur help ...
It worked fine ...
Only there is small typo in ur code ...the code is as follows for future ref for anyone...
OConv(IConv(Trim(tdAdmission_From.ReferralDate),"D"),"D/YMD[4,2,2]"):' 00:00:00'
thanks again ...
It was very very helpful...
One question:
Why we are using D here ..
format for D is
X = Iconv("31 DEC 1967", "D") X = 0
but my source date is not in 31 DEC 1967 format but 6/11/1992
Thanks ,
I really appreciate ur help ...
It worked fine ...
Only there is small typo in ur code ...the code is as follows for future ref for anyone...
OConv(IConv(Trim(tdAdmission_From.ReferralDate),"D"),"D/YMD[4,2,2]"):' 00:00:00'
thanks again ...
It was very very helpful...
One question:
Why we are using D here ..
format for D is
X = Iconv("31 DEC 1967", "D") X = 0
but my source date is not in 31 DEC 1967 format but 6/11/1992
Thanks ,
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
That's not a date, that's some flavor of a timestamp. Strip the date from the time before doing the conversion, me I would use the Field() function for that. Then you get to decide how to handle the time portion when it has been provided to you. ![Wink :wink:](./images/smilies/icon_wink.gif)
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers