my dog learned polymorphism
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
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: 33102

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!