cancel
Showing results for 
Search instead for 
Did you mean: 

RFC to JDBC Scenario, Date Transformation error

Former Member
0 Kudos

HI All,

I am trying a RFC to JDBC scenario where I am taking date from SAP in SAP Format i.e. 'yyyy-MM-dd' & sending this to oracle DB Where the format is 'MM/dd/yyyy'.

In Runtime Workbench I am getting Following error: 'java.sql.SQLException: ORA-01843: not a valid month'.

Can anybody help me on this.

Thanks & Regards,

Ruchi Agrawal

Accepted Solutions (1)

Accepted Solutions (1)

bhavesh_kantilal
Active Contributor
0 Kudos

Ruchi,

Unfortunately there is now way to see that

Guess you might try the solution using the JAVA code I suggested as well and check if it works. I think it should.

Regards,

Bhavesh

PS Do mark points for useful answers.

Former Member
0 Kudos

Hi Bhavesh,

The Date Problem is Solved. It is Working fine with your UDF code. There were some small changes in your UDF.

Here is the modified code:

String b="";

try

{

java.text.SimpleDateFormat sdf= new java.text.SimpleDateFormat("yyyy-MM-dd");

java.util.Date date = sdf.parse(a, new java.text.ParsePosition(0));

java.text.SimpleDateFormat sdf1= new java.text.SimpleDateFormat("dd-MMM-yy");

StringBuffer sb = new StringBuffer();

sdf1.format(date, sb, new java.text.FieldPosition(0));

b=sb.toString();

}

catch(Exception e)

{

e.printStackTrace();

}

return b;

Thanks,

Ruchi

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Ruchi,

Glad to know that it worked.

Do close the thread by awarding points.

Regards,

Bhavesh

Answers (6)

Answers (6)

Former Member
0 Kudos

Hi Ruchi,

Just check following weblog which shows how to convert sap r3 date format to oracle :

http://www.sdn.sap.com/irj/sdn/weblogs

Reagrds,

Keith

Former Member
0 Kudos

Hi Keith,

This link seems to be really helpful.

I created a UDF as

String s="to_date('2006-07-14','YYYY-MM-DD')";

return s;

I get an oracle exception : missing right parenthesis.

Thanks,

Ruchi

bhavesh_kantilal
Active Contributor
0 Kudos

Ruchi,

If you are following the way suggested in the blog, then you do not need an UDF.

What you need to do is, in the mapping of the DATE exactly like shown in the BLOG using the CONCAT function.

The only thing that will change are as follows,

1. Input DATE in the first CONCAT

2. And the format of your date in the second concat.

If source DATE is 19/05/05 then pass it as input to first concat and to next concat dd/mm/yy and so on.

Also do not forget to create the hasQuot attribute with the value NO.

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

I tried with CONCAT also....but still i get the same error of 'java.sql.SQLException: ORA-00907: missing right parenthesis' in Runtime Workbench.

Thanks,

Ruchi

former_member8655
Active Participant
0 Kudos

Hi Ruchi

The funstion is DateTrans function

it is available under Date Category.

Regards

Mitesh

Former Member
0 Kudos

Hi Mitesh,

I have already tried Date Trans in XI, but that doesn`t work.

Thanks & Regards,

Ruchi

Former Member
0 Kudos

Hi,

chk out this thread with the same issue

regards

jithesh

Former Member
0 Kudos

Hi Ruchi,

You can use the Graphical function DateTrans for the format of date. I think this should solve your problem.

Regards

Vishal Kumar

Former Member
0 Kudos

Hi Vishal,

I have already tried Date Trans in XI, but that doesn`t work. Still i continue getting the 'Invalid month' exception at Runtime Workbench.

Thanks & Regards,

Ruchi

bhavesh_kantilal
Active Contributor
0 Kudos

Ruchi,

Just check the format expected by the Oracle DB and use the Date Transformation Function in the fromat that it creates the date as expected by you.

We've used it and it works as needed.

I think Oracle expects the Date in the Format DD-MON-YY

Use Date transformation to convert to this format.

Regards,

Bhavesh

Message was edited by: Bhavesh Kantilal

Former Member
0 Kudos

HI Bhavesh,

The Oracle DB Format is '10-May-06'.

How will We use Date Trnsformation In this Case.

Thanks & Regards,

Chandravadan

bhavesh_kantilal
Active Contributor
0 Kudos

Hi,

Just looked up into the Datformat function. This is not available one option would be to write an UDF as follows where you pass the input date as a paramter and return the output date in oracle format

String strDATE="2006-05-18";
						Date date=dfold.parse(strDATE);
			SimpleDateFormat dfnew = new SimpleDateFormat("DD-MON-YY");
			String newdate=dfnew.format(dfnew);

return dfnew;

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

I use the the Java Code you have given for date Conversion in User Defined Simple Funtions.

I get error like 'cannot resolve symbol', i guess thats because we need to set the classPath and import the specific package, in that case how do i set the classpath and which packages do i need to import for that.

Thanks,

Ruchi

bhavesh_kantilal
Active Contributor
0 Kudos

Ruchi,

You need to import these 2 classes,

<b>import java.text.SimpleDateFormat;

import java.util.Date;</b>

They are a part of standard j2sdk and so, just give these import statements in your java code and it will work fine.

Regards,

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

Ruchi,

Also noticed a small error in my earlier code.

Just pass the input to the UDF as your input date(strDATE in my code) and then return the Oracle Date as newdate.

This is what it should be,


<b>String strDATE="2006-05-18";</b>		
Date date=dfold.parse(strDATE);
SimpleDateFormat dfnew = new SimpleDateFormat("DD-MON-YY");
String newdate=dfnew.format(date);
<b>return newdate;</b>

Regards,

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

Ruchi,

Can you check if this is what your <b>2nd Concat</b> contains,

<b> ','yy/mm/bb') </b>

You have missed a parantheisis to close the TO_DATE function and thats why you seem to be getting this error.

Just check this and let me know.

Regards,

Bhavesh

Former Member
0 Kudos

Bhavesh,

Its perfectly the smae way as the document says...i checked the expression so many times and i dont find anyhtin missin in that...

Is there a way we can see the INSERT statement that is created to insert data into DB , so that i can see my expression therein.

Thanks,

Ruchi

Former Member
0 Kudos

Hi Ruchi ,

U can use To_date(variavle_name , 'YYYY-MM-DD') function in u r SQL query to convert sap date format to oracle date format .

Regards,

Keith.

Former Member
0 Kudos

Hi Keith,

Thanks for the response.

I am trying to insert this date into Oracle through XI . I use an INSERT action for that. So i dont know where do i write this SQL Query in XI.

Thanks & Regards,

Ruchi

former_member8655
Active Participant
0 Kudos

Hi Ruchi

You might be performing graphical mapping.

so in graphical mapping we have a dateTrasformation function avaliable you can use that.

Regards.

Mitesh