-3

I'm new to Oracle 11g & I have a question about a query.

I have a table dummy which has created_date column of type Date.

I want a query which will return all the records where created_date + 7 days is less than today's date.

What type of query in Oracle 11g accomplishes this?

APC
  • 144,005
  • 19
  • 170
  • 281
Mike
  • 7,606
  • 25
  • 65
  • 82
  • 5
    i think you are lucky to get these good answers. you should at least attempt the query - or read a little about sql since this is pretty basic stuff. – Randy Jan 03 '12 at 01:42

3 Answers3

4

Oracle lets you use + for date arithmetic, so

where table.created_date >= sysdate
and table.created_date < sysdate + 7

will find rows between exactly now and exactly now plus 7 days.

If you don't want to include the time component, you can use the trunc() function

where trunc(table.created_date) >= trunc(sysdate)
and trunc(table.created_date) < trunc(sysdate) + 7
rejj
  • 1,216
  • 7
  • 13
  • Thanks! Can I use simply "where creation_date + 7 < sysdate" ? Why do we need 2 comparisons: created_date >= sysdate AND created_date < sysdate + 7?? – Mike Jan 02 '12 at 23:45
  • If you only check for `creation_date + 7 < sysdate` you'll get every record that matches that -- all the way back to the earliest record in your database. *If* that is what you actually want, then that is certainly a valid query – rejj Jan 03 '12 at 00:42
4

I think rejj's solution will give you the records between now and seven days in the future. From your description, it sounds like you probably want those records within the past seven days. I'd do this as:

WHERE created_date <= SYSDATE
  AND created_date >= SYSDATE - 7

This may be more clear, and is equivalent:

WHERE created_date BETWEEN SYSDATE AND (SYSDATE - 7)

Be aware that using TRUNC() will cause the optimizer to bypass any indexes you have on created_date, unless you have a functional index defined.

eaolson
  • 14,717
  • 7
  • 43
  • 58
0

Using INTERVAL constants can make date arithmetic clearer, as in

SELECT *
  FROM DUMMY
  WHERE CREATED_DATE >= TRUNC(SYSDATE) - INTERVAL '7' DAY

Share and enjoy.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110