Iconv oconv
Moderators: chulett, rschirm, roy
Iconv oconv
Im getting the date in the format yyyy-mm-dd and I want to be loaded in the target in the format mm-dd-yyyy. I tried using Iconv and oconv but its saying its not valid month may be the syntax im writing is not right.
Can anyone plz help me with the syntax to convert date
format yyyy-mm-dd to mm-dd-yyyy
Thanks for your time.
Can anyone plz help me with the syntax to convert date
format yyyy-mm-dd to mm-dd-yyyy
Thanks for your time.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Most likely you have an invalid date somewhere.
Try
Try
Code: Select all
OCONV(ICONV(In_MTRANS_DT,'D4-ymd'),'D4-mdy[2,2,4]')
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
seems like the date format in Oracle stage is not matching the string supplied, check if it is "mm-dd-yyyy" as the function will return value in this format.
Just out of curiosity, why you are changing format if its consistant and you are loading it to a database, just change the date format picture in insert/update statement to match incoming pattern.
Just out of curiosity, why you are changing format if its consistant and you are loading it to a database, just change the date format picture in insert/update statement to match incoming pattern.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
Im getting 6 digits dae (980501) and there are even 0 in the source so I need to convert the date in the Target as (MM-DD-YYYY) and store also if its 0 I need to load it as null.
The src datatype is DECIMAL and the target data type is DATE
So i have written If In_PMH_TRANS_DT = 0 Then @NULL Else ( If (In_PMH_TRANS_DT[1, 1]<='2') Then '20' :
In_PMH_TRANS_DT[2, 2] : '-' : In_PMH_TRANS_DT[4, 2]
: '-' : In_PMH_TRANS_DT[6, 2] Else '19' : In_PMH_TRANS_DT[1, 2] : '-' : In_PMH_TRANS_DT[3, 2] : '-' : In_PMH_TRANS_DT[5, 2])
This is converting the date to yyyy-mm-dd and now I want to convert it into target date format as mm-dd-yyyy for which I have written this
OCONV(ICONV(DSLink31.In_PMH_TRANS_DT),'D4-mdy[2,2,4]').
I have even tried using TO_DATE in the target insert statement with the format
(MM-dd-yyyy)
Thanks for yur time i responding to my post.
The errors Im getting are Attempt to convert String value "05-01-1995" to Date type unsuccessful
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Transformer_32: DBMS.CODE=ORA-01841
The src datatype is DECIMAL and the target data type is DATE
So i have written If In_PMH_TRANS_DT = 0 Then @NULL Else ( If (In_PMH_TRANS_DT[1, 1]<='2') Then '20' :
In_PMH_TRANS_DT[2, 2] : '-' : In_PMH_TRANS_DT[4, 2]
: '-' : In_PMH_TRANS_DT[6, 2] Else '19' : In_PMH_TRANS_DT[1, 2] : '-' : In_PMH_TRANS_DT[3, 2] : '-' : In_PMH_TRANS_DT[5, 2])
This is converting the date to yyyy-mm-dd and now I want to convert it into target date format as mm-dd-yyyy for which I have written this
OCONV(ICONV(DSLink31.In_PMH_TRANS_DT),'D4-mdy[2,2,4]').
I have even tried using TO_DATE in the target insert statement with the format
(MM-dd-yyyy)
Thanks for yur time i responding to my post.
The errors Im getting are Attempt to convert String value "05-01-1995" to Date type unsuccessful
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Transformer_32: DBMS.CODE=ORA-01841
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
If you have 6 digit dates - yymmdd, you can do something like below
In the Oracle stage, mention the format in To_Date() as YYYYMMDD.
This must solve your problem.
There is also century pivot possible in both datastage and oracle. You may choose one of them in your case to avoid all.
Code: Select all
svFormattedDate = If yourDate = 0 Then @NULL Else If yourDate[1,1] < '2' Then '20':yourDate Else '19':yourDate
This must solve your problem.
There is also century pivot possible in both datastage and oracle. You may choose one of them in your case to avoid all.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: