0

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?

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Richard Kettelerij
  • 2,049
  • 14
  • 17

1 Answers1

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')?

jarnbjo
  • 33,923
  • 7
  • 70
  • 94
  • Querying directly with a SQL client works like a charm. Since to_date('1916-05-01', 'YYYY-MM-DD') ignores the time component. However it's not clear how to do this from Java with EJB 2.x QL. – Richard Kettelerij Dec 02 '09 at 11:16