Oracle Datadiff

Question

(Oracle) I have to return all records from last 12 months. How to do that in PL/SQL?

EDIT: Sorry, I forgot to explain, I do have a column of DATA type

Answer 1

SELECT *
FROM   table
WHERE  date_column > SYSDATE - 365

Answer 2

I allready did that, but I don´t want last 365 days, I want last 12 months, there is a huge difference here.

Answer 3

SELECT *
FROM   table
WHERE  date_column > ADD_MONTHS(SYSDATE, -12)

Not sure I deserved down-modding for the earlier posts... was only trying to help.

Answer 4

Doing this in PL/SQL is pretty much synonymous with doing it in SQL.

SELECT *
FROM   table
WHERE  date_column >= ADD_MONTHS(TRUNC(SYSDATE),-12)

You might like to fiddle around with the TRUNC statement to get exactly the range you want -- I used TRUNC(SYSDATE) which is the same as TRUNC(SYSDATE,'D') -- ie. remove the time portion of the sysdate. For example, if it is currently Aug 12 but you want values from Feb 01 instead of Feb 12 then use:

SELECT *
FROM   table
WHERE  date_column >= ADD_MONTHS(TRUNC(SYSDATE,'MM'),-12)

Also, see the docs for treatment of months having different numbers of days: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions004.htm#SQLRF00603