About Oconv & Iconv

[loe_ram13]

At Source:
I have a '13-may-07' value stored as string.
After transformation ,I convert it to date as I have to load that data in to a table where that column datatype in the table is Date.
If I use
Oconv & Iconv then error message is thrown.
But If i change the target datatype to timestamp after transformation,same transformation logic works fine!!!!
Kindly help....

[ray.wurlod]

Be more specific about the arguments you used with Oconv() and Iconv(). Without that information it is impossible to provide any diagnosis. We also need the exact text of the error message that is thrown in your first example, and information about when it is thrown (compile time, run time, etc.).

[loe_ram13]

Be more specific about the arguments you used with Oconv() and Iconv(). Without that information it is impossible to provide any diagnosis. We also need the exact text of the error message that is t ...

My source is:
start_date varchar2 (10) (Format of data is:'12-may-07')
My desired target is s
start_date date (format should be '2007-05-12')
My transformation logic for the same is:

Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,3,2]"),"D-YMD[4,2,2]")

But if I keep the target datatype as Date,I am getting warning..
If I keep it as TIMESTAMP,it is getting solved.
Kindly help......

[chulett]

Your conversion seems fine. However, you haven't told us what your target database / table is, the nature of your sql nor what the actual error is that you get. So...

Database?
Target field type in database (not the job)?
SQL is generated or user defined? Post it.
Error when target field is declared as Date in job?

[ray.wurlod]

Iconv() doesn't need the format but, if given, it must be right. You have omitted "A" for the month.

Code: Select all

Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-YMD[4,2,2]") 

Easier to maintain would be

Code: Select all

Oconv(Iconv(ToT1.START_DATE,"D-DMY"),"D-YMD[4,2,2]") 

[chulett]

You have omitted "A" for the month.

Dang, missed that.

[loe_ram13]

Iconv() doesn't need the format but, if given, it must be right. You have omitted "A" for the month.

Code: Select all

Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-YMD[ ...[/quote]

My transformation logic is:
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-DMY[2,2,4]")

After transformation,My target database column has the datatype as Date.
I am using the following sql qery that is generated..

UPDATE #SchemaSum#.S_MCD_LASTCDR SET IN_VOICE_LAST_DT=TO_DATE(:2, 'DD-MON-YY'),RECORD_DATE=TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS') WHERE MIN_NO=:1;
INSERT INTO #SchemaSum#.S_MCD_LASTCDR (MIN_NO,IN_VOICE_LAST_DT,RECORD_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'))
I am getting the following warning.

ic_Mcd_update..T1: At row 987, link "To_MCD", while processing column "IN_VOICE_LAST_DT"
Value treated as NULL
Attempt to convert String value "29-05-2007" to Date type unsuccessful

In my target table that column is updated as NULL.
kindly help

[georgesebastian]

Iconv() doesn't need the format but, if given, it must be right. You have omitted "A" for the month.

Code: Select all

Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-YMD[ ...[/quote]

My transformation logic is:
Oconv(Iconv(ToT1.START_DATE,"D-DMY[2,A3,2]"),"D-DMY[2,2,4]")

After transformation,My target database column has the datatype as Date.
I am using the following sql qery that is generated..

UPDATE #SchemaSum#.S_MCD_LASTCDR SET IN_VOICE_LAST_DT=TO_DATE(:2, 'DD-MON-YY'),RECORD_DATE=TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS') WHERE MIN_NO=:1;
INSERT INTO #SchemaSum#.S_MCD_LASTCDR (MIN_NO,IN_VOICE_LAST_DT,RECORD_DATE) VALUES (:1,TO_DATE(:2, 'DD-MON-YY'),TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS'))
I am getting the following warning.

ic_Mcd_update..T1: At row 987, link "To_MCD", while processing column "IN_VOICE_LAST_DT"
Value treated as NULL
Attempt to convert String value "29-05-2007" to Date type unsuccessful

In my target table that column is updated as NULL.
kindly help[/quote]

Hi,

In the tranformer change the data type of the target date column to Varchar .I will work

Thanks
George

[chulett]

Your problem is the fact that you are converting the date from the format it is expecting to one that it can't use. Check your generated SQL - see the TO_DATE function and the mask it is expecting? Look familiar? What you have will work "as is", stop converting it.

[sachin1]

hello, as replied by George it works fine, also by changing data-type of date in OCI stage to timestamp, and without applying any format specification it will work.