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;
}

You use this literal string in Java? Or do you actually use a prepared statement or build a statetement with strings? — RealSkeptic, Jun 11 '18 at 14:00
Aha. Use a prepared statement. Do not build a statement from strings. Use setTimestamp to set your timestamp, and you won't need any `TO_TIMESTAMP` or format at all. — RealSkeptic, Jun 11 '18 at 14:03

score 7 · Accepted Answer · 2018-06-11T14:14:00.153

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'.

Thank you, I've never used this before since i'm still a beginniner, what am I supposed to use for "sql" in the preparedstatement ? — Hola, Jun 11 '18 at 14:10
Ah okay, it works but for some reason it now only gives me one booking even though there should be multiple — Hola, Jun 11 '18 at 14:30
I dont know what was wrong but it seems to be working now, thank you very much! — Hola, Jun 11 '18 at 15:02

Alex Poole · Answer 2 · 2018-06-11T14:09:55.737

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...

@Hola - it fixes the immediate error; but you really should be using prepared statements and bind variables. — Alex Poole, Jun 11 '18 at 14:20
I tried using the other method but it only returns one booking instead of all the bookings that are within this interval, I updated my post with my current code — Hola, Jun 11 '18 at 14:36

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

2 Answers2