I have an oracle query which returns next mont first sunday.
now i have a condition here to check if the date which has passed is first sunday of current month, then we need second sunday in current month.
Else, next month 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 1st july 2018, it has to return 8th july 2018(second sunday) else, any other day apart from first sunday like, (2nd july 2018), it has to return 5th Aug 2018.
Input Expected Output
01-Jul-18 08-Jul-18,
02-Jul-18 05-Aug-18,
05-Aug-18 12-Aug-18,
19-Aug-18 02-Sep-18.
Based on the description in your question and comments you want something like:
case when start_date = next_day(trunc(start_date, 'MM') - 1, 'Sunday') -- date is on first sunday
then next_day(start_date, 'Sunday') -- next Sunday, which is second in month
else next_day(last_day(start_date), 'Sunday') -- first Sunday of next month
end
With some sample dates in a CTE, including some discussed but also others:
with cte (start_date) as (
select date '2018-05-30' from dual
union all select date '2018-06-01' from dual
union all select date '2018-06-02' from dual
union all select date '2018-06-03' from dual
union all select date '2018-06-04' from dual
union all select date '2018-06-30' from dual
union all select date '2018-07-01' from dual
union all select date '2018-07-02' from dual
union all select date '2018-07-03' from dual
union all select date '2018-07-04' from dual
union all select date '2018-07-05' from dual
union all select date '2018-07-06' from dual
union all select date '2018-07-07' from dual
union all select date '2018-07-08' from dual
union all select date '2018-07-31' from dual
union all select date '2018-08-01' from dual
union all select date '2018-08-02' from dual
union all select date '2018-08-03' from dual
union all select date '2018-08-04' from dual
union all select date '2018-08-05' from dual
union all select date '2018-08-06' from dual
)
select start_date,
to_char(start_date, 'Dy') as day,
case when to_char(start_date, 'Dy') = 'Sun'
then 'Yes' else 'No' end as is_sunday,
case when start_date = next_day(trunc(start_date, 'MM') - 1, 'Sunday')
then 'Yes' else 'No' end as is_first_sunday,
next_day(trunc(start_date, 'MM') - 1, 'Sunday') as first_sun_this_month,
next_day(trunc(start_date, 'MM') + 6, 'Sunday') as second_sun_this_month,
next_day(last_day(start_date), 'Sunday') as first_sun_next_month,
case when start_date = next_day(trunc(start_date, 'MM') - 1, 'Sunday') -- date is on first sunday
then next_day(start_date, 'Sunday') -- next Sunday, which is second in month
else next_day(last_day(start_date), 'Sunday') -- first Sunday of next month
end as result
from cte;
gets
START_DATE DAY IS_ IS_ FIRST_SUN_ SECOND_SUN FIRST_SUN_ RESULT
---------- ------------ --- --- ---------- ---------- ---------- ----------
2018-05-30 Wed No No 2018-05-06 2018-05-13 2018-06-03 2018-06-03
2018-06-01 Fri No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-06-02 Sat No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-06-03 Sun Yes Yes 2018-06-03 2018-06-10 2018-07-01 2018-06-10
2018-06-04 Mon No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-06-30 Sat No No 2018-06-03 2018-06-10 2018-07-01 2018-07-01
2018-07-01 Sun Yes Yes 2018-07-01 2018-07-08 2018-08-05 2018-07-08
2018-07-02 Mon No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-03 Tue No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-04 Wed No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-05 Thu No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-06 Fri No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-07 Sat No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-08 Sun Yes No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-07-31 Tue No No 2018-07-01 2018-07-08 2018-08-05 2018-08-05
2018-08-01 Wed No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-02 Thu No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-03 Fri No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-04 Sat No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
2018-08-05 Sun Yes Yes 2018-08-05 2018-08-12 2018-09-02 2018-08-12
2018-08-06 Mon No No 2018-08-05 2018-08-12 2018-09-02 2018-09-02
The result column is the one you're interested in, the others just try to show the working a bit.
It looks odd to have the result dates out of sequence compared to the input dates - 2018-06-03 going to 2018--06-10 then those both before and after it go to 2018-07-01. But that seems to be what you want.
