java - How to process 0000-00-00 date in jdbc MySQL query

Question

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?

Answer 1

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.