CODEWITHSUNDEEP
oracle-databaseplsql
Saobi
Saobi

Reputation: 17051

Date Range in PL/SQL (Oracle)

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'

Upvotes: 7

Views: 37689

Answers (4)

Newbee so forgive me
Newbee so forgive me

Reputation: 1

This trunc the year but you get my gist:

SELECT (select trunc(sysdate - (SELECT TRUNC(SYSDATE) -
                                       add_months(trunc(sysdate, 'YEAR'), -24)
                                  FROM DUAL))from DUAL)+ (ROWNUM - 1) effective_date
                                  FROM DUAL
                                  CONNECT BY LEVEL <= (SELECT TRUNC(SYSDATE) - add_months(trunc(sysdate, 'YEAR'), -24)
                                  FROM DUAL);

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

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.

Upvotes: 15

Charles Bretana
Charles Bretana

Reputation: 146603

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')

Upvotes: 2

berlebutch
berlebutch

Reputation: 257

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

Upvotes: -2

Related Questions