ORA-01861: literal doesn't match format string

Question

In my application I passed date value (as string) as parameter to Servlet. In Servlet code, I converted the string to sql date as "2013-02-05" (yyyy-MM-dd).

Example: dat="05/02/2013" converted to sqlDate="2013-02-05". My servlet code is

 dat= request.getParameter("dat");
 //dat="05/02/2013";
     try
     {
         java.util.Date date=new SimpleDateFormat("dd/MM/yy").parse(dat);
         //out.println(date);
         sqlDate = new java.sql.Date(date.getTime());
         //out.println(sqlDate);
     }
     catch(Exception e)
     {

     }

if(div_code!=null)
{
try
{

 Class.forName("oracle.jdbc.driver.OracleDriver");
 java.sql.Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@xxx.xxx.x.xxx:xxx:pdsm", "test", "test");
 Statement stmt=conn.createStatement();
     ResultSet rs=stmt.executeQuery("insert into offer1 values('"+r_code+"','"+v_name+"','"+vcode+"','"+div_code+"','"+sec_code+"','"+extent+"','"+phone+"','"+sqlDate+"','"+loan+"','"+seeds+"','"+offerno+"','"+loc+"')");
      conn.setAutoCommit(true);
      out.println("data inserted");

   }catch(Exception e)
    {
       out.println(e);
    }
 }

When I'm calling this from android application, it shows error as "ORA-01861: literal doesn't match format string". The format I've used is shown correct result when I tested with sample input. But data didn't get inserted.

What is the problem in my code? Why am I receiving this error? Do I need to convert further? If yes, then how?

Also, someone explain can I use pl/sql function (to_date()) in servlet? Because it is easy to convert date

Edit: Modified Code:

String query="insert into offer1('RCODE','OFFERNO','DAT') values(?,?,?)"; 
PreparedStatement ps=conn.prepareStatement(query);
ps.setString(1,r_code);
ps.setString(2,offerno);
ps.setDate(3,sqlDate);
ResultSet rs=ps.executeQuery();
out.println("data inserted");

on Line #5, 05/02/2013 format should be `dd/MM/yyyy`, not `dd/MM/yy` — Aprian, Feb 06 '13 at 04:58
see @V.J. answer, line#5 is for parsing string `05/02/2013` to Date, for the desired result, you can use another `SimpleDateFormat` for formatting Date to String. — Aprian, Feb 06 '13 at 05:03
I don't need to format it to string. I've store date as database — Linga, Feb 06 '13 at 05:05
Please use the `[servlets]` tag only when you have a concrete problem related to `javax.servlet.*` classes. — BalusC, Feb 06 '13 at 13:26

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

First of all don't frame your query like this (appending everything into a single string).

It is vulnerable to SQL injection See SQL injection

So better use PreparedStatement as it will protect you from SQL injection and your conversion tasks for various values will be automatically taken care of.

So if you have a java.sql.Date variable sqlDate and now you want to insert it in the database: then you need these simple steps:

String query = "insert into your_table_name(`name`,`myDate`) values(?,?)";
PreparedStatement ps = conn.prepareStatement(query);
//now just set the values like this
ps.setString(1,"ABC");
//note index starts at 1
ps.setDate(2,sqlDate);
ResultSet rs = ps.executeQuery();

Now hope you can figure out how to do it for yourself.

For more information see this and this

edited May 23 '17 at 12:23

Community

1
1

answered Feb 06 '13 at 05:03

Abubakkar

15,488
8
55
83

I think it will be useful. I'll try now – Linga Feb 06 '13 at 05:07
how did you frame the query string? – Abubakkar Feb 06 '13 at 05:26
try this query `insert into offer1(\`RCODE\`,\`OFFERNO\`,\`DAT\`) values(?,?,?)` Notice the backticks (`\``) for the column name – Abubakkar Feb 06 '13 at 05:52
I tried this. insert into offer1(RCODE,OFFENO,DAT). Work well – Linga Feb 06 '13 at 06:01

score 0 · Answer 2 · answered Feb 06 '13 at 04:59

0

//String date = "05/02/2013";
dat= request.getParameter("dat");
SimpleDateFormat dateFormater = new SimpleDateFormat("dd/MM/yyyy");
Date testDate = null;
try {
    testDate = dateFormater.parse(dat);
}catch(Exception ex){
    ex.printStackTrace();
}

From "dd/MM/yyyy" to "yyyy/MM/dd"

SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
String convertedDate = formatter.format(testDate);
System.out.println("Your converted Date :- "+convertedDate);

answered Feb 06 '13 at 04:59

V.J.

9,492
4
33
49

I've already converted by line #5 and #7. Why I'm receiving error when inserting the date to database? – Linga Feb 06 '13 at 05:02
I don't need to format it to string. I've store date as database – Linga Feb 06 '13 at 05:04

score 0 · Answer 3 · answered Feb 06 '13 at 05:03

I can see several problems in your code

1) remove Class.forName("oracle.jdbc.driver.OracleDriver"), DriverManager knows how to find the necessary driver on the classpath

2) use conn.prepareStatement, see API

3) remove conn.setAutoCommit(true); conn is in autocommit mode by default

4) use stmt.executeUpdate instead of stmt.executeQuery

ORA-01861: literal doesn't match format string

3 Answers3