sqloracle-databaseplsqloracle11g

How to find number of Sundays in a given year (ex: input_year = 1996) and also the dates of those Sundays


How to find number of Sundays in a given year in Oracle SQL.

Input: 1996

Expected output:

<date-of-sunday-1>
<date-of-sunday-2>
.............
.............
<date-of-sunday-n>
<count-of-no-of-sundays-in-that-year>

Solution

  • This is how to fetch Sundays:

    SQL> with the_whole_year as
      2    (select trunc(to_date(&&par_year, 'yyyy'), 'yyyy') + level - 1 c_date,
      3            to_char(trunc(to_date(&&par_year, 'yyyy'), 'yyyy') + level - 1, 'fmday') c_day
      4     from dual
      5     connect by level <= add_months(trunc(to_date(&&par_year, 'yyyy'), 'yyyy'), 12) -
      6                         trunc(to_date(&&par_year, 'yyyy'), 'yyyy')
      7    )
      8  select c_date
      9  from the_whole_year
     10  where c_day = 'sunday';
    
    C_DATE
    ----------
    07.01.1996
    14.01.1996
    21.01.1996
    28.01.1996
    04.02.1996
    11.02.1996
    <snip>
    

    I suppose you'll be able to count them yourself.


    [EDIT: a function that does the counting]

    SQL> CREATE OR REPLACE FUNCTION f_count_of_sundays (par_year IN NUMBER)
      2     RETURN NUMBER
      3  IS
      4     retval   NUMBER;
      5  BEGIN
      6     WITH the_whole_year
      7          AS (    SELECT TRUNC (TO_DATE (par_year, 'yyyy'), 'yyyy') + LEVEL - 1
      8                            c_date,
      9                         TO_CHAR (
     10                            TRUNC (TO_DATE (par_year, 'yyyy'), 'yyyy') + LEVEL - 1,
     11                            'fmday')
     12                            c_day
     13                    FROM DUAL
     14              CONNECT BY LEVEL <=
     15                              ADD_MONTHS (
     16                                 TRUNC (TO_DATE (par_year, 'yyyy'), 'yyyy'),
     17                                 12)
     18                            - TRUNC (TO_DATE (par_year, 'yyyy'), 'yyyy'))
     19     SELECT COUNT (*)
     20       INTO retval
     21       FROM the_whole_year
     22      WHERE c_day = 'sunday';
     23
     24     RETURN retval;
     25  END;
     26  /
    
    Function created.
    
    SQL> select f_count_of_sundays(1996) from dual;
    
    F_COUNT_OF_SUNDAYS(1996)
    ------------------------
                          52
    
    SQL>