Oracle query to return first Sunday of current month

Question

I have a query to return first Sunday of current month. However, if first Sunday fall on the first day of the month, it gets the next Sunday instead. How could get rid of it to return the first Sunday that on the first day of the month?

Take 1 Aug, 2021 as an example,below query returns second Sunday, 8 Aug 2021.

SELECT TO_CHAR(NEXT_DAY(TRUNC(TO_DATE(('20210801'), 'yyyyMMdd'), 'MM'), 'Sunday'), 'yyyyMMdd') AS FIRST_SUNDAY FROM DUAL;

Answer 1

Move one day back, to the last day of previous month:

SQL> SELECT TO_CHAR (
  2            NEXT_DAY (TRUNC (TO_DATE (('20210801'), 'yyyyMMdd'), 'MM') - 1,
  3                      'sunday'),                                   --   ^^
  4            'yyyyMMdd') AS FIRST_SUNDAY                            -->  this
  5    FROM DUAL;

FIRST_SU
--------
20210801

SQL> SELECT TO_CHAR (
  2            NEXT_DAY (TRUNC (TO_DATE (('20210701'), 'yyyyMMdd'), 'MM') - 1,
  3                      'sunday'),
  4            'yyyyMMdd') AS FIRST_SUNDAY
  5    FROM DUAL;

FIRST_SU
--------
20210704

SQL>