PostgreSQL 11 spring JPA query error with java.sql.Date object and TO_DATE fucntion with format YYYY-DD-MM

Question

I am passing java.sql.Date object to CrudRepository native query. This date object is passed to a date function with a format ('YYYY-DD-MM'). This query is working with PostgreSQL 9.4, but it gives error when the database is PostgreSQL 11. I tried JDBC driver v42.2.11 and 9.1-901-1. Both are giving same result.

The error message is:

ERROR: date/time field value out of range: "2020-05-27 +00:00"

Code:

@Query(value = "SELECT TO_DATE(?1, 'YYYY-DD-MM')", nativeQuery = true)
public String sampleQuery(java.sql.Date date);

When I gave the date format as "SELECT TO_DATE(?1, 'YYYY-MM-DD')", that time it worked in PostgreSQL 11.

Any idea what is happening here.

Calling to_date() on a value that is already a date makes no sense whatsoever. So if you pass a java.sql.Date instance to the query, then your query makes no sense. Did you mean to use to_char() in order to format the data? — user330315
– user330315, Commented May 28, 2020 at 15:50
By the way, the terrible java.sql.Date class was supplanted years ago by the modern java.time classes defined in JSR 310, specially LocalDate. Both JPA 2.2 and recent Hibernate have been updated to support java.time types. — Basil Bourque
– Basil Bourque, Commented May 28, 2020 at 22:25

pifor · Accepted Answer · 2020-05-28 15:53:15Z

1

Because 27 is not a valid month it should not work:

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

postgres=# select to_date('2020-05-27 +00:00','YYYY-DD-MM');
ERROR:  date/time field value out of range: "2020-05-27 +00:00"
postgres=#

And if it works in 9.4 it gives an unexpected result:

postgres=# select version();
                                                    version                                                     
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.26 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

postgres=# select to_date('2020-05-27 +00:00','YYYY-DD-MM');
  to_date   
------------
 2022-03-09
(1 row)

postgres=#

answered May 28, 2020 at 15:53

pifor

7,9722 gold badges12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nowhere Man Over a year ago

Impressive catch!

Collectives™ on Stack Overflow

PostgreSQL 11 spring JPA query error with java.sql.Date object and TO_DATE fucntion with format YYYY-DD-MM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related