Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle Date problem

 
Sandeep Awasthi
Ranch Hand
Posts: 597
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

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

Regards
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rajesh,
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.
 
Sandeep Awasthi
Ranch Hand
Posts: 597
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 597
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 98
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * from emp where TRUNC(date_of_birth) = ?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic