How to find all records that are older than or equal to the current month of the previous year?

I barely know SQL. I am trying to achieve the following:

For example I have the following table that has 2 fields

NAME VARCHAR(20)
BDAY TIMESTAMP(6)

I need to find all whose birthdays are current month of the previous year and prior. For example if I am running the query on 08/15/2021, I need to find all records that have a month/date of 08/2020 (ignoring the day part) and prior. So some entries may be less than 12 months old (those after the day of query in the current month of prior year) and would get need to get included in the returned rows.

if I have the following records and I am running the query on 8/15/2021

Mark 12/22/2018
Mike 10/15/2019
Joe  07/31/2020
John 08/06/2020
Jill 08/28/2020
Bill 08/31/2020
Jack 09/01/2020
Jeb  08/08/2021

It needs to return the following:

Mark 12/22/2018
Mike 10/15/2019
Joe  07/31/2020
John 08/06/2020
Jill 08/28/2020
Bill 08/31/2020

If I did something like the following,

select * from BDAY where BDAY < add_months(systimestamp,-12) order by BDAY desc;

it would exclude the entries for the current month of previous year that are after the date when the query is run. I can get the desired result using convoluted queries but I was wondering if there was an easier way of doing this, e.g. an oracle function.

Answer

You can compare with the first day of next month, ie 11 months ago instead of 12:

select * from bday where bday<trunc(add_months(sysdate ,-11), 'mm');

Full example with test data:

alter session set nls_date_format='mm/dd/yyyy';
with bday(name, bday) as (
select 'Mark',to_date('12/22/2018','mm/dd/yyyy') from dual union all
select 'Mike',to_date('10/15/2019','mm/dd/yyyy') from dual union all
select 'Joe ',to_date('07/31/2020','mm/dd/yyyy') from dual union all
select 'John',to_date('08/06/2020','mm/dd/yyyy') from dual union all
select 'Jill',to_date('08/28/2020','mm/dd/yyyy') from dual union all
select 'Bill',to_date('08/31/2020','mm/dd/yyyy') from dual union all
select 'Jack',to_date('09/01/2020','mm/dd/yyyy') from dual union all
select 'Jeb ',to_date('08/08/2021','mm/dd/yyyy') from dual
)
select * from bday where bday<trunc(add_months(sysdate ,-11), 'mm');

NAME BDAY
---- ----------
Mark 12/22/2018
Mike 10/15/2019
Joe  07/31/2020
John 08/06/2020
Jill 08/28/2020
Bill 08/31/2020

6 rows selected.

DBFIDDLE: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b9f4889a47f59f6a48d3fb2d0a212617