on 05-18-2006 5:49 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi Ruchi
The funstion is DateTrans function
it is available under Date Category.
Regards
Mitesh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ruchi,
You can use the Graphical function DateTrans for the format of date. I think this should solve your problem.
Regards
Vishal Kumar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ruchi
You might be performing graphical mapping.
so in graphical mapping we have a dateTrasformation function avaliable you can use that.
Regards.
Mitesh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
72 | |
26 | |
10 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.