I’m getting this exception:
java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date
Originating from this code:
Date internalDate = rs.getDate(idx++);
Where rs
is a ResultSet
.
So this is fine to me – I know there are zero’ed dates in the database and I need to be able to read these and convert them into an appropriate (probably null) data in my downstream data structures. The problem is I don’t know how to retrieve it and get a “soft” error. I thought about wrapping this line in a try/catch for SQLException but understand this will break validity of the ResultSet.
Is it possible to read this value in another way without throwing a SQLException?
Advertisement
Answer
I like @a_horse_with_no_name‘s answer, however if you don’t have control over the connection, you could change the query to return a null
instead:
select ... case when my_date_col = '0000-00-00' then null else my_date_col end as my_date_col, ...
or the slightly more terse, but mysql-only, option:
if(my_date_col = '0000-00-00', null, my_date_col) as my_date_col
Also, caution is advised changing the entire application’s JDBC behaviour as you may break code that relies on such dates being returned – perhaps they use rs.getString(i)
instead. You would have to regression test all other queries to be sure.