-- Count of number of Sundays in the current month to date

SELECT  COUNT(*) sunday_count
FROM
  (SELECT level idx
   FROM  DUAL
   CONNECT BY level <= SYSDATE - TRUNC(SYSDATE,'MON')
  )
WHERE  TO_CHAR(TRUNC(SYSDATE,'MON')+ idx-1,'DY') = 'SUN'
;

-- Count of number of Sundays in the first six months of year 2002

SELECT  COUNT(*) sunday_count
FROM 
  (SELECT level idx
   FROM  DUAL
   CONNECT BY level <= TO_DATE(20020630,'yyyymmdd') - TO_DATE(20020101,'yyyymmdd') 
  )
WHERE  TO_CHAR(TO_DATE(20020101,'yyyymmdd') + idx-1,'DY') = 'SUN'
;

-- Count of number of Sundays using all_objects table (based on Oracle version the 
-- all_objects table can have about 80k rows)

SELECT  COUNT(*) sunday_count
FROM 
  (SELECT rownum idx
   FROM  all_objects
   WHERE  rownum <= TO_DATE(20020630,'yyyymmdd') - TO_DATE(20020101,'yyyymmdd') 
  )
WHERE  TO_CHAR(TO_DATE(20020101,'yyyymmdd') + idx-1,'DY') = 'SUN'
;