Thanks for the question, rajitha.
Asked: September 01, 2009 - 4:19 am UTC
Last updated: September 01, 2009 - 2:22 pm UTC
Version: Oracle 10.0.2
Viewed 10K+ times! This question is 
You Asked
I want to know how to write a SQL query that searches for a given time range in oracle. for example I have two fields in the table - FromDate (DATE field) and Todate( Date field) and the values for them in the format 3/18/2009 11:59:00 AM.
As an example say the FromDate is 8/20/2009 10:00:00 PM and ToDATE is 8/31/2009 1:00:00 AM
The challenge here is to check if a certain date field (call it SearchDate that has a value for ex 8/25/2009 11:00:00 PM exists in the time range mentioned (between FromDate and ToDATE above)....the logic should first look at the date portion and that is easy to do..the next part is to compare the times like if 11:00:00 PM is between 10:00:00 PM and 1:00:00 AM which is where I need assistance. I tried to convert the time to a number but that doesn't work since the query would do something like "is 1100 between 1000 and 100.
Any other suggestions or expert opinion will greatly help !
and Tom said...
... and the values for them in the format 3/18/2009 11:59:00 AM. ...
no, it is not stored in that format, that is a display format, they are stored in a 7 byte binary field that includes in order:
century
year
month
day
hour
minute
second
... the logic should first look at the date portion and that is easy to do.. ...
no, not true, not correct. The logic should simply:
where to_date(:bind_variable,'mm/dd/yyyy hh:mi:ss am')
between fromDate and toDate;
that is all - just use sql and just use dates and just compare.
Is this answer out of date? If it is, please let us know via a Comment