Java ora-01830 date format picture ends before converting entire input string Timestamp SQL

Question

I'm having problems getting dates from my database while using Oracle through java. If I use this in Java:

SELECT * 
from HA2_BOOKINGS 
WHERE ROOM like 'R1' 
  AND BEGINNING >= (TO_TIMESTAMP('2018-06-11 15:11:43.208', 'YYYY-MM-DD HH24:MI:SSXFF')) 
  AND END <=  (TO_TIMESTAMP('2018-06-11 15:11:43.208', 'YYYY-MM-DD HH24:MI:SSXFF'))

I get the ora-01830 error but when I use the exact same thing in oracle itself the code works. Does anyone know why?

My java code:

public Collection<Booking> getAllBookingsForRoomInPeriod(Room r, String startDate, String endDate) {
        Collection<Booking> bookingsForRoomInPeriod = new ArrayList<Booking>();
        SimpleDateFormat parser = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        parser.setLenient(false);

        Date dateBeginning = new Date();
        Date dateEnd = new Date();
        try {
            dateBeginning = parser.parse(startDate);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            dateEnd = parser.parse(endDate);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        Timestamp tsBeginning = new Timestamp(dateBeginning.getTime());
        Timestamp tsEnd = new Timestamp(dateEnd.getTime());

        bookingList.clear();

        Connection aCon = Persistence.getConnection();
        ResultSet resultSet = null;

        // Raeume lesen
        try {
            resultSet = Persistence.executeQueryStatement(aCon, "SELECT * from HA2_BOOKINGS WHERE ROOM like '" + r.getRoomNr() + "' AND BEGINNING >= (TO_TIMESTAMP('" + tsBeginning + "', 'YYYY-MM-DD HH24:MI')) AND END <=  (TO_TIMESTAMP('" + tsEnd + "', 'YYYY-MM-DD HH24:MI'))");
            while (resultSet.next()) {
                Booking a = new Booking();
                a.setRoom(resultSet.getString("ROOM"));
                a.setUsername(resultSet.getString("USERNAME"));
                a.setDescription(resultSet.getString("DESCRIPTION"));
                a.setBeginning(resultSet.getTimestamp("BEGINNING"));
                a.setEnd(resultSet.getTimestamp("END"));
                this.bookingList.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Persistence.closeConnection(aCon);
        }
        System.out.println(bookingList);

        return bookingList;
}

Answer 1

Do not pass parameters like that.

"' AND BEGINNING >= (TO_TIMESTAMP('" + tsBeginning + "', 'YYYY-MM-DD HH24:MI')) will use Java's toString() method do convert the Timestamp instance to a String - and that will most likely not match the format mask you have specified.

Use placeholders and pass the Timestamp instances directly:

First you need to a SQL query that uses placeholders ? instead of values directly:

String sql = "SELECT * from HA2_BOOKINGS WHERE ROOM = ? AND BEGINNING >= ? and END <= ?"

Then prepare the statement:

PreparedStatement pstmt = aCon.prepareStatement(sql);

alternatively use it without a variable:

PreparedStatement pstmt = aCon.prepareStatement("SELECT * from HA2_BOOKINGS WHERE ROOM = ? AND BEGINNING >= ? and END <= ?");

Supply the values:

pstmt.setString(1, 'R1');
pstmt.setTimestamp(2, tsBeginning);
pstmt.setTimestamp(3, tsEnd);

Execute the query (using the supplied values) and process the result set.

resultSet = pstmt.executeQuery();
while (resultSet.next()) {
  ..
}

Apart from the performance improvements (because you avoid a costly hard-parse in Oracle) this is also a secure way of doing that as it prevents SQL injection.

Note that I replaced the useless LIKE operator with = because LIKE 'R1' is the same as = 'R1'.

Answer 2

The X in your format model is the local radix character. Your Java and 'oracle' (whichever client that refers to) sessions appear to have different NLS settings.

You would see the same thing in, say, SQL*Plus if you ran this first:

alter session set nls_numeric_characters=',.';

You can modify the settings so Java behaves the same, probably via the locale; but it's better not to rely on or assume NLS settings if you can avoid them. If your literals always have period you might as well use those in the format model too instead of the NLS-dependent X:

SELECT * 
from HA2_BOOKINGS 
WHERE ROOM like 'R1' 
  AND BEGINNING >= TO_TIMESTAMP('2018-06-11 15:11:43.208', 'YYYY-MM-DD HH24:MI:SS.FF')
  AND END <= TO_TIMESTAMP('2018-06-11 15:11:43.208', 'YYYY-MM-DD HH24:MI:SS.FF')

Or even better, use bind variables and pass a timestamp value directly, as @a_horse_with_no_name suggested.

---

Having now seen your Java code and how you're embedding the literals with concatenation, definitely follow that advice; this just explains why you got the error...