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 Oracle Date  problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle Date  problem" Watch "Oracle Date  problem" New topic

Oracle Date problem

Sandeep Awasthi
Ranch Hand

Joined: Oct 23, 2003
Posts: 597

Please help me.

I want to select records from the Oracle table for particular date.

for ex. suppose I have table with columns emp_id and date_of_birth

and prepared statement as select * from emp where data_of_birth = ?

how do I select emp records which comes on particular date ?

I tried to set using Calendar and java.sql.Date

preparedStamend.setDate(1,new java.sql.Date(calendar.getTime().getTime()));

but because oracle stores time also , the date does not match. Please kindly help me. I want records on particular date ignoring time part of date


Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Try using a java.sql.Timestamp.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32819

Since the field in the database has a time value, you should test for a range:

select * from emp where date_of_birth > ? and date_of_birth < ?

Then set the parameters to the very end of yesterday and the very beginning of tomorrow.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Sandeep Awasthi
Ranch Hand

Joined: Oct 23, 2003
Posts: 597
Hi Jeanne,

Thanks for reply.

I dont want to select record in range of date like > somedate <otherdate.

I want to select records on specific date ( = specific_date).

I can do it with to_char(oracle_date,'dd-mm-yyyy') = string_date in dd-mm-yyyy format.

I just wanted to know if I can avoid oracle's to_char function to trim time part of the date.

Thank you very much once again for reply.
Sandeep Awasthi
Ranch Hand

Joined: Oct 23, 2003
Posts: 597
Hi Jeanne

Thanks for reply. I am sorry in the beginning when I read it, I could not properly understand what you mean. But now I understood it. Thanks very much.
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
select * from emp where TRUNC(date_of_birth) = ?
I agree. Here's the link:
subject: Oracle Date problem
It's not a secret anymore!