Error with date conversion

[Former Member]

Hi,

I receive a date from an IDOC in the format 20061005160300. This date must be converted to insert it in an Oracle database. I wrote a little UDF which cuts the string to 8 characters and puts a TO_DATE(' before and a ','YYYY-MM-DDD') after it.

Now I get an Oracle error because somehow there is a blank (after the TO_DATE') in the final string:

<DATUM_VB hasQuot="No">TO_DATE(' 2006092','YYYY-MM-DD')</DATUM_VB>

.

Here is my UDF:

int length = date.length();
String sub = new String();
String before = new String("TO_DATE('"); 
String after = new String("','YYYY-MM-DD')");
if(length >= 8 && !date.equals("00000000"))
{
sub = before+date.substring(0, 8)+after;
}
else{
sub = "";
}
return sub;

Do you have any idea why there is a blank in it? If I test the mapping there's no error and no blank...

Regards

Dominic

[former_member206604 · Accepted solution]

Hi Dominic,

As Michal said use <b>Trim</b> functionality in your mapping. But not after your UDF, you need to use it before passing the date to the UDF.

date>Trim>UDF

Also you can add this line in your UDF in the first line.

date = date.trim();

Thanks,

Prakash

[Former Member · Accepted solution]

Hi all,

thanks for your help. The trim function (at the right place) solved the problem.

Regards

Dominic

[Former Member]

Hi Dominic,

Alternatively use Transform fn under Date Function..

Double click on the built in fn and in the property area u have to key key source and target date Format

give as follows..

Source Date Format: yyyyMMddHHmmss

Target Date Format: yyyy-MM-dd

Use built in Concat fun further to construct ur To_date function.

Regards,

Sudharshan

[Shabarish_Nair]

else try

int length = date.length();

String sub = new String();

//String before = new String("TO_DATE('");

//String after = new String("','YYYY-MM-DD')");

if(length >= 8 && !date.equals("00000000"))

{

sub = "TO_DATE('".concat(date.substring(0, 8));

sub = sub.concat("','YYYY-MM-DD')");

}

else{

sub = "";

}

return sub;

[MichalKrawczyk]

hi,

can you try using <b>Trim</b> function (from standard TEXT functions) after your UDF ?

if this will not work maybe the error is not

telling the correct message ?

Regards,

michal

[Former Member]

Hi Michal,

unfortunatly the trim-function doesn't help. The error mus be somewhere else, becaus the data is correct when testing it (Test tab) and also in the queue. But if I check in SXMB_MONI I get an Oracle error (java.sql.SQLException: ORA-01861: literal does not match format string) and I have a blank in the result string.

Regards