Iconv oconv

[balu1625]

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.

[Sainath.Srinivasan]

Why don't you post what you have tried so others can guide you ?

[balu1625]

oconv(iconv(In_MTRANS_DT,'d4'),'d-ymd[4,2,2]')

[ArndW]

Most likely you have an invalid date somewhere.

Try

Code: Select all

OCONV(ICONV(In_MTRANS_DT,'D4-ymd'),'D4-mdy[2,2,4]')

[balu1625]

Thanks for replying. I check the date format and its all with yyyy-mm-dd and I have tried the syntax that was posted.

Now I get the error ORA-01861: literal does not match format string

[Sainath.Srinivasan]

That is an Oracle error.

Are you loading the result into Oracle table ? Check whether you have a TO_DATE() function and post the command as it appears.

[priyadarshikunal]

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.

[balu1625]

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

[arunkumarmm]

Balu, I think you had the same question in a different post. Please refer your old post

[Sainath.Srinivasan]

If you have 6 digit dates - yymmdd, you can do something like below

Code: Select all

svFormattedDate = If yourDate = 0 Then @NULL Else If yourDate[1,1] < '2' Then '20':yourDate Else '19':yourDate 

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.

[ray.wurlod]

What is "plz"?

In German it's the abbreviation for Postleitzahl (postal code), but you're not writing in German.