I've got the problem to solve.
To find nearest(next or previous) date of the event. It happens every leap year at the particular date. Let's say 1st of May. How to generate such dates and get the nearest of them?
Thank you.
Our request for more information:
Just to clarify, the requirement is: Inputs: - a known event date, eg "May 1st", which only happens on a leap year - a nominated date, "eg Mar 12th 2011" Task: Find the nearest event date to the nominated date. Yes ?
My addition information:
Task is more like: Find the nearest event date to the current date. Like SYSDATE.
So here is an example where the event_date = May 12th. Let's get all the May 12th's 4 years either side of any particularly nominated year.
SQL> select add_months(date '2019-05-12',-48+rownum*12) dte,
2 to_number(to_char(add_months(date '2019-05-12',-48+rownum*12),'yyyy')) yy
3 from dual connect by level <= 8;
DTE YY
--------- ----------
12-MAY-16 2016
12-MAY-17 2017
12-MAY-18 2018
12-MAY-19 2019
12-MAY-20 2020
12-MAY-21 2021
12-MAY-22 2022
12-MAY-23 2023
8 rows selected.
Now we can limit that to just leap years
SQL> select *
2 from (
3 select add_months(date '2019-05-12',-48+rownum*12) dte,
4 to_number(to_char(add_months(date '2019-05-12',-48+rownum*12),'yyyy')) yy
5 from dual connect by level <= 8
6 )
7 where mod(yy,4)=0 and ( mod(yy,100) != 0 or mod(yy,400) = 0 );
DTE YY
--------- ----------
12-MAY-16 2016
12-MAY-20 2020
And then we just find which of those two is nearest to a date of interest (in this case, sysdate)
SQL> select dte
2 from (
3 select add_months(date '2019-05-12',-48+rownum*12) dte,
4 to_number(to_char(add_months(date '2019-05-12',-48+rownum*12),'yyyy')) yy
5 from dual connect by level <= 8
6 )
7 where mod(yy,4)=0 and ( mod(yy,100) != 0 or mod(yy,400) = 0 )
8 order by abs(sysdate-dte)
9 fetch first 1 rows only;
DTE
---------
12-MAY-16