Oracle SQL - Select date between month-current year(-1) to month-current year

Question

I have a column with a date in it as well as other columns in the table. I want to be able to:

show all rows that match the date of having September 1st of the previous year to July 30th of the current year.

I know some of what needs to be done just not sure on the specific syntax of the dates..

SELECT * FROM xx
WHERE ASM_DATE BETWEEN TRUNC(SYSDATE-1,'YY') AND TRUNC(SYSDATE,'YY');

That's what I have so far..

I know I can use SYSDATE and 'YYYY' to get the current year and then do that -1 for the previous year, I'm unsure how to specify the months in addition to that however. Any help would be great.

Answer 1

SELECT
  *
FROM
  xxx
WHERE
      ASM_DATE >= ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -12)
  AND ASM_DATE <  ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'),   1)

Answer 2

Take a look a the oracle ADD_MONTHS function.

There's a complete listing of datetime functions in the oracle reference.