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.
Advertisement
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