Date Range in PL/SQL (Oracle)

Question

If I have table with a Date column called myDate, with values like 2009-08-25 09:00:09.0.

I want to select all rows for Aug 25, from 12:00:01 AM until 11:59:59 PM and NOTHING for Aug 26. Is it sufficient to simply use the condition:

where myDate between Date '2009-08-25' and Date '2009-08-26'

And I want to select all rows BEFORE Aug 25, not including Aug 25. Can I do:

where myDate < Date '2009-08-25'

Answer 1

if you want data for the full day 25 and excluding all the 26, you need to remove the first second of the 26:

where myDate >= Date '2009-08-25' and myDate < Date '2009-08-26'

or

where myDate between Date '2009-08-25' and Date '2009-08-26' - interval '1' second

Update -- A little precision: In Oracle the DATE datatype is used both for 'Date' types with or without time. If you're dealing with dates without time, it is usually stored with a 00:00 time segment, meaning midnight. Because of this, the first between (my_date between '2009-08-25' and '2009-08-26') will potentially select two full days.

By removing the first second of the 26, you ensure that you won't inadvertently select rows from the 26.