JDBC - ORA-01861: literal does not match format string 01861

Question

I have problem with retrieving a data from oracle data base
I want to access data by using two JDatechoosers...
I use JDatechooser to store date value in database of type date.

chooser=new JDateChooser();
chooser.setBounds (200, 175, 175, 25);
chooser.setDateFormatString("dd-MM-yyyy");
pstmt.setDate(5, new java.sql.Date(chooser.getDate().getTime()));

I want to access data between two dates using two JDatechooser values as fallows..

    chooser = new JDateChooser();       
    chooser.setBounds (100, 15, 100, 25);
    chooser.setDateFormatString("dd-MM-yyyy");
    chooser.addFocusListener (this);

    chooser1 = new JDateChooser();      
    chooser1.setBounds (220, 15, 100, 25);
    chooser1.setDateFormatString("dd-MM-yyyy");
    chooser1.addFocusListener (this);

   ResultSet rs = st.executeQuery("SELECT * FROM Bill WHERE B_DATE BETWEEN '"+new java.sql.Date(chooser.getDate().getTime())+"' AND '"+new java.sql.Date(chooser1.getDate().getTime())+"' ");

I get the error as

SQL Error: ORA-01861: literal does not match format string 01861

please help me to solve this

Answer 1

Never pass DATE or TIMESTAMP values as as Strings.

Use a PreparedStatement and pass an instance of java.sql.Date or java.sql.Timestamp

PreparedStatement pstmt = conn.prepareStatement(
    "SELECT * FROM Bill WHERE B_DATE BETWEEN ? and ?");

pstmt.setDate(1,new java.sql.Date(chooser.getDate().getTime()));
pstmt.setDate(2,new java.sql.Date(chooser1.getDate().getTime()));

ResultSet rs = st.executeQuery();
while (rs.next()) {
   ....
}

That way you never have to worry about formatting a date or timestamp value.

As an Oracle DATE is actually a timestamp, you might be better off using a timestamp value.