fetch records of last 90 days from current date in sql

Question

Hi am using below query to retrieve records last 90 days from current date but its not working. please help me.

SELECT * 
FROM adhoc_request 
WHERE DATEDIFF(("d", Crdate, current_date()) < 90);

Answer 1

SELECT * 
FROM adhoc_request 
WHERE Crdate >= sysdate-90

SYSDATE includes the time portion, e.g. "04-13-2013 09:45:51"

So in effect, the query run on "04-13-2013 09:45:51" translates to

WHERE Crdate >= "2013-01-13 09:45:51"

David's suggestion works more predictably and is in most cases more correct, which first truncates SYSDATE to return just today's date before taking 90 days off it, i.e.

SELECT * 
FROM adhoc_request 
WHERE Crdate >= TRUNC(sysdate)-90

Answer 2

select *
from adhoc_request
where Crdate < DATEADD("d", -90, current_date()) < 90);

Some notes:

• Never use a function on a predicate in a WHERE clause. Note CrDate is alone in my code
• DATEADD will extract exactly 90 days including time. DATEDIFF counts midnight boundaries