"Date format picture ends.." error while executing a select via JDBC PreparedStatement

Question

Working against an Oracle Database. It has a DATE typed column and the select(simplified here for easy perusal) statement issued via JDBC PreparedStatement is as follows:

select  DATE1 from TYPE_TABLE  where TYPE_TABLE.DATE1 =  ?

The question mark parameter value at runtime is 2018-01-31 12:50:30.0 PST. We are setting the parameter via setTimeStamp API of the PreparedStatement class.The error that we are seeing on execution of the above select query is as follows:

java.sql.SQLDataException: ORA-01830: date format picture ends before 
converting entire input string 

I did search over SO and other sister sites of SE network and all the references I found are related to Inserting dates into the database without using proper format mask. But I didn't find anything related to select statements yet.

What could be the possible problem and the solution here?

P.S: The type of the DATE1 column is indeed of type DATE in the Oracle DB.

Edit: If the NLS_DATE_FORMAT parameter is the issue here, then what format should be used for the date value in question (i.e2018-01-31 12:50:30.0 PST) here?

Answer 1

tl;dr

You have not provided any code, so we cannot decipher the exact problem. But read on for some guidance.

Wrong data-type: Your problem may be due to passing a moment in UTC (java.sql.Timestamp) for comparison to a column of type DATE which lacks any zone/offset (not in UTC, not in any zone/offset).

Use JDBC 4.2 or later. Pass java.time objects rather than strings.

myPreparedStatement.setObject( 
    … , 
    LocalDateTime.of( 
        LocalDate.of( 2018 , Month.January , 23 ) , 
        LocalTime.of( 12 , 34 , 56 ) 
    ) 
)

Oracle DATE type

As others noted, the DATE type in Oracle 11g etc. is not a date-only value (it has a time-of-day), and is not defined as such in standard SQL. It is a date-time lacking zone/offset, perhaps analogous to SQL-standard TIMESTAMP WITHOUT TIME ZONE type. This means such a value is not a specific moment, only a rough idea about potential moments along a range of about 26-27 hours.

For a date-time value lacking any concept of time zone or offset-from-UTC, the class to use in Java is LocalDateTime.

LocalDate ld = LocalDate.of( 2018 , Month.January , 23 ) ;
LocalTime lt = LocalTime.of( 12 , 34 , 56 ) ;
LocalDateTime ldt = LocalDateTime.of( ld , lt ) ;

ldt.toString(): 2018-01-23T12:34:56

Tip: Keep in mind this is not a moment. If you want a moment, you’ll need to place this LocalDateTime in the context of a time zone (or offset-from-UTC). Do so by applying a ZoneId to get a ZonedDateTime object. Search Stack Overflow for more info and examples.

We are setting the parameter via setTimeStamp API of the PreparedStatement class.

A java.sql.Timestamp is for a moment, a specific point on the timeline, in UTC. The equivalent type in standard SQL is TIMESTAMP WITH TIME ZONE. This type is not appropriate for your Oracle DATE column – as discussed above, a Oracle DATE is not a moment.

Furthermore, you should no longer be using java.sql.Timestamp as it is now legacy, supplanted by the java.time.Instant class.

To retrieve a Oracle DATE value into Java:

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

Smart objects, not dumb strings

If the NLS_DATE_FORMAT parameter is the issue here, then what format should be used for the date value in question (i.e2018-01-31 12:50:30.0 PST) here?

Do not use strings at all. Exchange java.time objects with Oracle database using a JDBC driver compliant with JDBC 4.2 or later.

As of JDBC 4.2 and later, you can directly exchange java.time objects. No need for strings. No need for java.sql classes ever again when working with date-time values.

Avoid legacy date-time classes such as Date, Calendar, and Timestamp. These are terribly confusing, poorly designed, and all-around troublesome. Use only java.time classes.

For a SQL such as:

SELECT some_date FROM some_table  where some_date =  ? ;

…pass your LocalDateTime the placeholder to your PreparedStatement by calling setObject.

myPreparedStatement.setObject( … , ldt ) ;

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

• Java SE 8, Java SE 9, and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and Java SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • Later versions of Android bundle implementations of the java.time classes.
  • For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 2

Oracle stores date using 7 bytes. One byte each for Century, Year, Month, Day, Hours, Minutes and Seconds. So any string must be passed with a corresponding format string to specify how each component of that string will be parsed into the 7 byte date.

select DATE1 
  from TYPE_TABLE  
 where TYPE_TABLE.DATE1 = to_date('2018-01-31 12:50:30', 'yyyy-mm-dd hh24:mi:ss');

If no format is specified, Oracle uses the default format available in NLS_DATE_FORMAT parameter. If the default format does not match the string used, we get this error :

ORA-01830: date format picture ends before  converting entire input string

Answer 3

Oracle is not SQL compliant: The data type DATE contains hours, minutes and seconds.

Documentation says:

This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

A timestamp should only be necessary if you go down to split seconds and/or you want to store timezone information.

Can you try with setDate()?

Answer 4

A fair guess is that your JDBC driver will convert the Oracle Data type into a java.sql.TimeStamp or java.sql.Date type (and note that there are differences between java.util.Date and java.sql.Date, in terms of precision for example). In the case, for example, of a java.sql.Timestamp type, try specifying:

ret.value = java.sql.Timestamp(java.util.Date().getTime()); copy to clipboard
and see if this helps. Then you will be able to use:
ret.value = java.sql.Timestamp(system.parseDate(work.getString("yourDate"), 
    "yyyyMMddHHmmssz").getTime());