SQL to Determine if Current Week is Week Containing N Friday of Month

Question

I am looking for some SQL to determine if the current week is the week that contains the Nth Day of the month.

Example. I want to know if I am in the week that contains the third Friday that will occur this month. Or which week of the month contains the third Friday. Weeks should be defined as starting on a Sunday and ending on a Saturday. So a month that starts on a Saturday would not see it's Friday until the 4th week, where as a month that starts Sunday - Friday, would see it's 3rd Friday in the 3rd week of the month.

Not sure if PL/SQL is required for this or not.

Answer 1

All the modern SQL platforms have an abundance of date/time functions for doing date arithmetic. But when I look at SQL that's built on them, especially for requirements like yours, my eyes glaze over.

I use a carefully crafted calendar table instead. There are two things I like a lot about using it.

• Relatively unskilled interns can quickly learn to query it correctly.
• Queries can usually be seen to be obviously correct.

Here's what a query against my calendar table would look like if you were trying to answer the question, "Does the current week contain the third Friday of the current month?" (My calendar table uses ISO week numbers, not strict calendar weeks. Two workarounds below.)

select cal_date
from calendar
where year_of_date = 2012 
  and iso_week = (select iso_week
                  from calendar
                  where cal_date = current_date)
  and day_of_week = 'Fri'
  and day_of_week_ordinal = 3;

It returns no rows; the current week (2012-08-19 to 2012-08-25) doesn't contain the third Friday of August 2012. (It contains the 4th Friday.)

Without changing my table at all, I can answer your first question with this query. It wraps your definition of a week in a common table expression. If I had to use something like this in production, I'd probably create a view rather than a CTE.

with current_week as (
  select *
  from calendar
  where cal_date between (select max(cal_date) 
                          from calendar 
                          where day_of_week = 'Sun' 
                            and cal_date <= current_date) 
                     and (select min(cal_date) 
                          from calendar 
                          where day_of_week = 'Sat' 
                            and cal_date >= current_date)
)
select cal_date
from current_week
where day_of_week = 'Fri'
  and day_of_week_ordinal = 3;

Again, it returns no rows; same reason.

A second alternative is to define your own week_number column to replace my iso_week column. Then you could express your query much like the first one above.

---

DDL for a calendar table in PostgreSQL. The DDL is standard SQL; I'm not certain that the CHECK constraints on iso_year and iso_week are standard SQL, though. Add indexes that are appropriate for your environment.

Includes a function to populate the table. Should be easy to port to Oracle.

create table calendar (
  cal_date date primary key,
  year_of_date integer not null 
    check (year_of_date = extract(year from cal_date)),
  month_of_year integer not null 
    check (month_of_year = extract(month from cal_date)),
  day_of_month integer not null 
    check (day_of_month = extract(day from cal_date)),
  day_of_week char(3) not null 
    check (day_of_week = 
    case when extract(dow from cal_date) = 0 then 'Sun'
         when extract(dow from cal_date) = 1 then 'Mon'
         when extract(dow from cal_date) = 2 then 'Tue'
         when extract(dow from cal_date) = 3 then 'Wed'
         when extract(dow from cal_date) = 4 then 'Thu'
         when extract(dow from cal_date) = 5 then 'Fri'
         when extract(dow from cal_date) = 6 then 'Sat'
    end),
  day_of_week_ordinal integer not null
    check (day_of_week_ordinal = 
      case
        when day_of_month >= 1 and day_of_month <= 7 then 1
        when day_of_month >= 8 and day_of_month <= 14 then 2
        when day_of_month >= 15 and day_of_month <= 21 then 3
        when day_of_month >= 22 and day_of_month <= 28 then 4
        else 5
      end),
  iso_year integer not null 
    check (iso_year = extract(isoyear from cal_date)),
  iso_week integer not null 
    check (iso_week = extract(week from cal_date))

);


CREATE OR REPLACE FUNCTION insert_range_into_calendar(from_date date, to_date date)
  RETURNS void AS
$BODY$

DECLARE
    this_date date := from_date;
BEGIN

    while (this_date <= to_date) LOOP
        INSERT INTO calendar (cal_date, year_of_date, month_of_year, day_of_month, day_of_week, day_of_week_ordinal, iso_year, iso_week)
        VALUES (this_date, extract(year from this_date), extract(month from this_date), extract(day from this_date),
        case when extract(dow from this_date) = 0 then 'Sun'
             when extract(dow from this_date) = 1 then 'Mon'
             when extract(dow from this_date) = 2 then 'Tue'
             when extract(dow from this_date) = 3 then 'Wed'
             when extract(dow from this_date) = 4 then 'Thu'
             when extract(dow from this_date) = 5 then 'Fri'
             when extract(dow from this_date) = 6 then 'Sat'
        end,
        case when extract(day from this_date) between 1 and 7 then 1
             when extract(day from this_date) between 8 and 14 then 2
             when extract(day from this_date) between 15 and 21 then 3
             when extract(day from this_date) between 22 and 28 then 4
             when extract(day from this_date) > 28 then 5
        end,
        cast(extract(isoyear from this_date) as integer),
        cast(extract(week from this_date) as integer));
        this_date = this_date + interval '1 day';
    end loop;       

END;
$BODY$
  LANGUAGE plpgsql 

Answer 2

You can simply generate the date ranges for the weeks that contain the Nth Day of each month, e.g. the 3rd Friday of each month, with a query like this:

select d - day_of_week AS sunday
      ,d + (7 - day_of_week) AS saturday
from   (select trunc(sysdate,'YY')+rownum-1 AS d
              ,to_number(to_char(trunc(sysdate,'YY')+rownum-1,'D'))
               AS day_of_week
        from dual connect by level <= 366)
where  to_char(d,'W') = 3
and    to_char(d,'DY') = 'FRI';

SUNDAY      SATURDAY
==========  ==========
14/01/2012  21/01/2012
11/02/2012  18/02/2012
10/03/2012  17/03/2012
14/04/2012  21/04/2012
12/05/2012  19/05/2012
09/06/2012  16/06/2012
14/07/2012  21/07/2012
11/08/2012  18/08/2012
15/09/2012  22/09/2012
13/10/2012  20/10/2012
10/11/2012  17/11/2012
15/12/2012  22/12/2012

EDIT: You can create a simple function to check a single date, e.g.:

CREATE FUNCTION date_in_week_of_nth_day
   (in_date IN DATE
   ,in_week IN NUMBER
   ,in_day  IN VARCHAR2
   ) RETURN CHAR IS
  ret CHAR(1);
BEGIN
  select 'Y' into ret
  from   (select trunc(in_date,'MM')+rownum-1 AS d
                ,to_number(to_char(trunc(in_date,'MM')+rownum-1,'D'))
                 AS day_of_week
          from dual connect by level <= 31)
  where  to_char(d,'W') = in_week
  and    to_char(d,'DY') = in_day
  and    :in_date between (d - day_of_week) and (d + (7 - day_of_week));
  RETURN ret;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 'N';
END;