Oracle query for getting particular Date

Question

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.

Answer 1

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.