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:59PM 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

Hi Saobi,

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.

Answer 2

SELECT   *
FROM   TABLE
 WHERE   myDate BETWEEN myDate  ('08/25/2009', 'MM/DD/YYYY')
                         AND  myDate  ('08/26/2009', 'MM/DD/YYYY')

Answer 3

To get everything between Aug 25, at 12:00:01 AM until 11:59:59 PM, inclusive, try this:

  Where myDate Between to_Date('yyyymmddhh24miss', '20090825000001') 
                  And  to_Date('yyyymmddhh24miss', '20090825235959')

(Why are you excluding midnight on the 25th ?? That first second (00:00:00) is part of the 25th as well... )

To get everything before Aug 25, try this:

Where myDate < to_Date('yyyymmdd', '20090825')