File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Problem in retrieving data with java.util.Date() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Problem in retrieving data with java.util.Date()" Watch "Problem in retrieving data with java.util.Date()" New topic

Problem in retrieving data with java.util.Date()


Joined: Aug 17, 2001
Posts: 8
When we insert the Date to Database (Oracle), we store java.util.Date which stores the date with time as follows:
_stmt.setTimestamp(1, new Timestamp(new java.util.Date().getTime()),Calendar.getInstance(TimeZone.getTimeZone("GMT")));
The record which was inserted on Aug 17th would have the insert date stored like this
yyyy-mm-dd hh:mm:ss:nnn
2001-08-17 15:11:15.049
The problem is, when we need to view this record from the GUI screen where user will pick up the From and To date (which are type of java.util.Date).
Assuming that user has selected both From and To date (which is of type java.util.Date)as the Aug 17th and Database has one record with Aug 17th date.
My retrieval SQL looks as follows:
select insertDt from table where insertDt>=? and insertDt<=?;
Option 1:
_stmt.setDate(1, new java.sql.Date(getUtilFromDateFromGUI().getTime()),Calendar.getInstance(TimeZone.getTimeZone("GMT")));
_stmt.setDate(2, new java.sql.Date(getUtilToDateFromGUI().getTime()),Calendar.getInstance(TimeZone.getTimeZone("GMT")));
option 2:
_stmt.setTimestamp(1, new TimeStamp(getUtilFromDateFromGUI().getTime()),Calendar.getInstance(TimeZone.getTimeZone("GMT")));
_stmt.setTimestamp(2, new Timestamp(getUtilToDateFromGUI().getTime()),Calendar.getInstance(TimeZone.getTimeZone("GMT")));
When I tried both option 1 and 2, and do not retrive the record from database.
Any help?

Butch Car

Joined: Jan 12, 2001
Posts: 13
On the GUI is the user selecting the time portion as well or
just the date? Does the created query look something like this:

select insertDt from table
where insertDt>=2001-08-17
and insertDt<=2001-08-17;
If the GUI just presents the date you may tack on time strings
to java.util.Date calls to the query ends up like:
select insertDt from table
where insertDt between 2001-08-17 00:00:00
and 2001-08-17 23:59:59
Note: since you are calling setTimeStamp I didn't bother
to wrap the above dates in a to_date function. I just wanted
the sample to be easy to read.
Hope this helps,

Joined: Aug 17, 2001
Posts: 8
GUI sends me the date with time(like java.util.Date). I tried using java.text.Dateformat.
java.util.Date incomingFromDate = datefromGUI
String fromDateStr = DateFormat.format(incomingFromDate ;
//manipulate fromDateStr to have time as 00 :00 AM.
incomingFromDate = Date.parse(fromDateStr);
// similarly for incmoingToDate. It will have time as 12:00 AM
My SQL has insert_dt>= incomingFromDate and insert_dt<= incmoingToDate.
It worked.
I agree. Here's the link:
subject: Problem in retrieving data with java.util.Date()
It's not a secret anymore!