# Using SQL to find the number of Sundays in a month

## I want to find out how many Sundays come in any month in SQL.

I want to use SQL to find out how many Sundays come in any month. Please help me.

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

, interval 10*tens.i + units.i day )
from integers as tens
cross
join integers as units
, interval 10*tens.i + units.i day )
, interval 1 month )
order

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(
, interval 10*tens.i + units.i day )
) = 1
then 1 else null end )  as sundays
from integers as tens
cross
join integers as units
, interval 10*tens.i + units.i day )