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>
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>