wood burning stoves 2.0*
The moose likes JDBC and the fly likes Date comparison Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Date comparison" Watch "Date comparison" New topic
Author

Date comparison

Winston Smith
Ranch Hand

Joined: Jun 06, 2003
Posts: 136
Hi all,
Here's the scenario:
I have a java.sql.Date, i.e. 10/20/2003 5:00:00.
I want to look up this date (specifically the time) in an Oracle database.
The date is stored in the database as an Oracle DATE. Now, when I set a java.sql.Date to the same date, and look it up, it's not finding it. I basically want to find the DATE in the database that has a matching time (i.e. 5:00:00).
Both date objects have the same time value, I'm sure of that. Any ideas how to compare a java.sql.Date (with time value) to an Oracle DATE?
Thanks,
WS


for (int i = today; i < endOfTime; i++) { code(); }
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
You might want to try the 'java.sql.Timestamp' class instead. The 'java.sql.Date' object doesn't guarantee support of hour/minute/second values. Don't worry that the Oracle type is "DATE"; the 'java.sql.Timestamp' will work just fine, using the "getTimestamp()" and "setTimestamp()" methods. Just to be safe, you might want to call "setNanos(0)" to make sure the nano-seconds are zero-ed out before doing the comparison.
Alternatively you could do a "to_char(...)" on the date in the SQL, and then use a SimpleDateFormat to format the date (or timestamp) in Java and do a simple "=" comparison.
sunitha reghu
Ranch Hand

Joined: Dec 12, 2002
Posts: 937
Originally posted by Wayne L Johnson:

Alternatively you could do a "to_char(...)" on the date in the SQL, and then use a SimpleDateFormat to format the date (or timestamp) in Java and do a simple "=" comparison.


to_char or to_date?
Winston Smith
Ranch Hand

Joined: Jun 06, 2003
Posts: 136
Hi Wayne,
First, I'll try the timestamp option. If I understand correctly, I can simply call getTimeStamp() on it, even though the Oracle type is Date. This is cool and would be the preferable solution. Also I will try to implement the SimpleDateFormat method you've proposed. Essentially, I need to examine the database and find the date that matches, so I suppose I can do a to_char() on each date, then compare it to the string produced by SimpleDateFormat. A theoretically sound idea, but somewhat more sluggish than the former. Thanks for your help!
WS
[ October 27, 2003: Message edited by: Winston Smith ]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Hi Winston,
I saw no mention in your post about which version of Oracle you are using.
For your information, the mappings between Oracle database types and java classes are detailed in the Oracle9i JDBC Developer's Guide and Reference.
(There are also versions of this guide for other Oracle versions at the Tahiti Web site.)
As far as I know, in Oracle 8i, the DATE datatype maps to "java.sql.Timestamp", however in 9i, DATE maps to "java.sql.Date".
Also for your information, you can find lots of helpful information at these Web sites:
http://technet.oracle.com
http://asktom.oracle.com
Good Luck,
Avi.
P.S. By the way, you aren't related to the hero of George Orwell's novel, 1984, are you? (I suppose everybody asks you that one, don't they?)
[ October 27, 2003: Message edited by: Avi Abrami ]
Winston Smith
Ranch Hand

Joined: Jun 06, 2003
Posts: 136
Hello Avi,
Hahaha, actually, there must not be many Orwell fans since you are the first to make the association! Anyhow, I'm using 9i and I haven't been able to try any of the suggestions yet (this problem has moved to the back burner), but I appreciate your direction and I'll take a look at the sites. Thanks again, All
WS
 
 
subject: Date comparison