Q

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

                  
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?

This was first published in July 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close