Querying record by date (01 may 1916) on Oracle using Java/EJB 2.x

Question

I have an EJB 2.1 entity bean that queries a "date of birth" column in an Oracle table. The column in the database is of type DATE. The query works without problems except for one specific date: 01 may 1916.

This has something to do with daylight savings. Oracle stores the date as: 5/1/1916 1:00:00 AM, while the time of all other dates is set to 12:00:00 AM (e.g. 1/7/1971 12:00:00 AM).

On the production/acceptation systems a query (with EJB-QL!) for 01 may 1916 return no results. The strange thing is that it does work on my development/test system. I've checked the database settings on the various systems and found the DBTIMEZONE parameter was set to +00:00 on the dev/test boxes and to +02:00 on the production/acceptation boxes. However changing the timezone to +02:00 yields no difference.

What could cause this behavior? How to search for this date of birth?

Answer 1

Just a wild guess: You are in Belgium and the date was written incorrectly to the database, so that changing the timezone settings later, won't make any difference when querying?

In Belgium and a few other European countries, DST was introduced on May 1st 1916 by skipping the period from 12AM to 1AM, so May 1st, 12:00AM did in fact not exist. If the database is populated by Java software, I would assume that the Java program already wrote 1AM to the database. What happens if you skip the Java component and use an SQL client to insert and query a "time-less" value to/from the column, e.g. to_date('1916-05-01', 'YYYY-MM-DD')?