Using SQL to find the number of Sundays in a month

Here's one technique in brief:

1. to identify a specific month, construct a date for the first day of that month
2. use the INTEGERS table to generate a series of dates beginning with the first day of that month, to cover all dates in the month
3. use a date function to determine if the generated date is a Sunday
4. use COUNT() on the result of the test for Sunday

To create your integers table, use this:

create table integers 
     ( i integer not null primary key );
insert into integers (i) values (0);
insert into integers (i) values (1);
insert into integers (i) values (2);
insert into integers (i) values (3);
insert into integers (i) values (4);
insert into integers (i) values (5);
insert into integers (i) values (6);
insert into integers (i) values (7);
insert into integers (i) values (8);
insert into integers (i) values (9);

Now let's say the month you're interested in is September of 2006. So you would set the initial date as '2006-09-01' and then generate all the dates in that month using the following query. Note: every database system has different date functions, so be sure you know how to use yours. This example uses MySQL syntax:

set @day1 = '2006-09-01';  

                  
select date_add(@day1 
          , interval 10*tens.i + units.i day ) 
                      as adate 
  from integers as tens
cross
  join integers as units
 where date_add(@day1 
          , interval 10*tens.i + units.i day )
     < date_add(@day1 
          , interval 1 month )
order
    by adate  

As you can see, there is an ORDER BY clause, so that you can convince yourself that this query is generating all the dates in the month correctly.

Now just add the function to determine a Sunday, and do your count:

set @day1 = '2006-09-01';         

                         
select count(
        case when dayofweek(
          date_add(@day1 
          , interval 10*tens.i + units.i day )
                           ) = 1
             then 1 else null end )  as sundays 
  from integers as tens
cross
  join integers as units
 where date_add(@day1 
          , interval 10*tens.i + units.i day )
     < date_add(@day1 
          , interval 1 month )  

Result:

sundays
    4 

Simple, yes?