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

score 4 · Answer 1 · 2017-03-30T12:26:00.857

4

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.

edited Mar 30 '17 at 12:26

answered Mar 30 '17 at 12:20

I think the broader message here is to use a prepared statement and bind variables over building dynamic SQL, for security reasons. – unleashed Mar 30 '17 at 17:28
@unleashed: only partially. It's also about passing correct data types and thus avoiding the evil implicit data type conversion and it's about performance because prepared statements avoid hard parsing on the server. – Mar 30 '17 at 20:28
1

I agree. Storing or treating dates as string has to be one of my top pet peeves. – unleashed Mar 31 '17 at 13:51

score 0 · Answer 2 · answered Sep 06 '18 at 04:28

whenever you want to Insert a date insert it with the help of PrepareStatement class because that has in inbuilt function to convert java.util.date (a whole timestamp with names and numbers )---->convert it to java.sql.Date(2018-09-06 format) and then use this sql date to preparedStatement as ps.setDate(format will become : 09-jan-1996) which is acceptable by oracle.

Note: while converting util date to sql date pass time not the whole date.

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

2 Answers2