I have an Oracle query which returns next month first Sunday. Now I have a condition here to check if the next month is having 1st day as Sunday then it needs to return next Sunday. Else, next month's first sunday.
My query:
DEF startDate = to_date('somedate', 'dd/mm/yyyy');
Select next_day(last_day(&startDate),'Sunday' ) from dual ;
Expected output: if we input july 2018, it has to return 5th August 2018(first sunday). if it is june 2018, then it has to return july 8th (not 1st july whic is first day and its sunday)
Input Expected Output
Jul-18 05-Aug-18,
Aug-18 02-sep-18,
June-18 08-jul-18,
because 1st day is sunday, so we are considering next Sunday 8th July.
If the date you pass in to next_day() is already the first Sunday you'll get the second Sunday, so pass in the first day of next month. You can get that by finding the first of the current month and adding a month; or by finding the last day of the current month and adding a day:
with cte (start_date) as (
select date '2018-05-14' from dual
union all select date '2018-06-15' from dual
union all select date '2018-07-16' from dual
union all select date '2018-08-17' from dual
)
select start_date,
next_day(add_months(trunc(start_date, 'MM'), 1), 'Sunday') as result1,
next_day(trunc(start_date, 'MM') + interval '1' month, 'Sunday') as result2,
next_day(last_day(start_date) + 1, 'Sunday') as result3
from cte;
START_DATE RESULT1 RESULT2 RESULT3
---------- ---------- ---------- ----------
2018-05-14 2018-06-03 2018-06-03 2018-06-03
2018-06-15 2018-07-08 2018-07-08 2018-07-08
2018-07-16 2018-08-05 2018-08-05 2018-08-05
2018-08-17 2018-09-02 2018-09-02 2018-09-02
If the 'start date' is always going to be the first of the month then you don't need the trunc(..., 'MM') part; that takes any day in the month and returns midnight on the 1st of that month.