Re: Search by a specific date - how?????

random_at_interaccess.com (Brett Neumeier) writes:

>Graham Leggett (graham_at_vwv.com) wrote:
>: I am having a very frustrating time trying to convince oracle to give me
>: all the columns in a table where the date is a specific date.
 

>"Date" fields store the time as well as the date. Perhaps the
>R_REPORT_DATE field in the database actually has a time component
>as well, in which case there will be no match with your date.
>I suggest: "...where trunc(R_REPORT_DATE) =..."

But this will make it impossible to use an index on the date field to speed up the search time and will likely slow down the search in general, which could be a factor if the table is large. Two alternatives:

where R_REPORT_DATE >= to_date('1998-02-03','yyyy-mm-dd') and R_REPORT_DATE < to_date('1998-02-04','yyyy-mm-dd')

For no particularly good reason, I prefer:

where R_REPORT_DATE between to_date('1998-02-03','yyyy-mm-dd') and to_date('1998-02-03','yyyy-mm-dd')+.999999

1/86400 is .00001157, so adding .999999 produces a time component of 23:59:59, which works because the level of time granularity that Oracle uses is one second. (This is probably bad programming practice, but to me the latter is more readable than the former.)

As far as I have been able to determine, Oracle optimizes both of the above SQL code snips the same so they should produce similar speed searches.

--
Mike Nolan