Oracle WHERE Clause to Select Last 12 Months

Here is a quick Oracle WHERE Clause that allows you to select the last 12 months worth of data. This is useful in reporting scenarios when you want to generate a report for a relative period of time, independent of what day of the week the report was actually run.

[In my original post, I used a hard-to-read version of a query. Mr. Patrick Kramer set me straight. I also needed to adjust the query to return the previous month, regardless of the current date the query was run.]

New Query

SELECT *

FROM MyTable

WHERE MyDateField BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -13), 'MM')

AND TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1))+1)

Old Query

SELECT *

FROM MyTable

WHERE MyDateField BETWEEN TO_DATE (

TO_CHAR (ADD_MONTHS (SYSDATE, -13), 'MM')

|| '-01-'

|| TO_CHAR (ADD_MONTHS (SYSDATE, -13), 'YYYY'),

'MM-DD-YYYY')

AND (LAST_DAY (

TO_DATE (

TO_CHAR (SYSDATE - 1, 'MM')

|| '-01-'

|| TO_CHAR (SYSDATE - 1, 'YYYY'),

'MM-DD-YYYY')))