CODEWITHSUNDEEP
sqloracle-database
NK369
NK369

Reputation: 1

Select dates older than time frame SQL

I am trying to find all records in a database with an admission date which is older than a certain time frame (in this case, all admission dates older than 4 days old).

I have:

     select memberid, admitdate
     from membertable
     where admitdate < (sysdate-4)

As a result, I'm getting a lot of admission dates which match this, but I'm ALSO getting dates which are from only 2 days ago, so that doesn't match my code. What am I doing wrong?

If it helps, the admit dates have a format of mm/dd/yyyy.

Upvotes: 0

Views: 191

Answers (2)

Alex Poole
Alex Poole

Reputation: 191425

Dates, including sysdate, have a time component. Even if all your admitdate values are at midnight that is still a time, and sysdate is only going to be at midnight if you run your query then.

select sysdate, sysdate-4, trunc(sysdate), trunc(sysdate)-4 from dual;

SYSDATE             SYSDATE-4           TRUNC(SYSDATE)      TRUNC(SYSDATE)-4   
------------------- ------------------- ------------------- -------------------
2018-06-21 16:44:53 2018-06-17 16:44:53 2018-06-21 00:00:00 2018-06-17 00:00:00

If you filter your records on sysdate-4 then that will include any admitdate values up to, in this example, 2018-06-17 16:44:53; so presumably all the records for the 17th if they are actually all midnight.

with membertable (memberid, admitdate) as (
            select 1, date '2018-06-15' from dual
  union all select 2, date '2018-06-16' from dual
  union all select 3, date '2018-06-17' from dual
  union all select 4, date '2018-06-18' from dual
  union all select 5, date '2018-06-19' from dual
  union all select 6, date '2018-06-20' from dual
  union all select 7, date '2018-06-21' from dual
)
select memberid, admitdate
from membertable
where admitdate < (sysdate-4);

  MEMBERID ADMITDATE          
---------- -------------------
         1 2018-06-15 00:00:00
         2 2018-06-16 00:00:00
         3 2018-06-17 00:00:00

If you truncate the value you're comparing against then its time portion will also be treated as midnight, so you'll only match record up to - but not including - that point in time, 2018-06-17 00:00:00:

with membertable (memberid, admitdate) as (
            select 1, date '2018-06-15' from dual
  union all select 2, date '2018-06-16' from dual
  union all select 3, date '2018-06-17' from dual
  union all select 4, date '2018-06-18' from dual
  union all select 5, date '2018-06-19' from dual
  union all select 6, date '2018-06-20' from dual
  union all select 7, date '2018-06-21' from dual
)
select memberid, admitdate
from membertable
where admitdate < trunc(sysdate)-4;

  MEMBERID ADMITDATE          
---------- -------------------
         1 2018-06-15 00:00:00
         2 2018-06-16 00:00:00

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270503

admitdate should be a date. You seem to be suggesting it is a string. You can try:

where to_date(admitdate, 'MM/DD/YYYY') < trunc(sysdate) - 4;

You can then fix the data in the table, so it is stored as a date.

Upvotes: 0

Related Questions