Has anyone here had any dealings with queries that use a timestamp in a where clause hitting an Oracle 9i database? The problem I am having is that I cant seem to find a format for the date or timestamp that Oracle is happy is with. I have used java.sql.Timestamp, java.sql.Date, java.util.Date, Java.util.Calendar.getTime(). The oracle Timestamp format has been set to 'yyyy-mm-dd hh:MM:ss.fff' and that is now how Timestamp elements appear in the database, but I still can't seem to get oracle to like my query timestamp format.
The following generates an ORA-01849 Hour must be between 1 -12
select * from foo where myDate >= 2004-01-12 00:00:00.000 and myDate <= 2004-01-12 10:10:00.000
Note that the hour is cetainly less than 12, so I am at a loss at what format Oracle is expecting. java.sql.Timestamp seems to match the format, yet.....
Any way, if anyone has tackled this problem and has a solution I am all ears.