Re: Date for a week day of a month

On Jul 3, 2007, at 13:27 , Emi Lu wrote:

> Can I know how to get the date of each month's last Thursday please?
>
> For example, something like
>
> Query:  select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
> Result: 2007-04-26

Using a function for calculating the first occurrence of a particular
day of the week in a month from a previous post[1], this should work.
Natural language processing left as an exercise to the reader :)

CREATE OR REPLACE FUNCTION first_dow_of_month(DATE, INTEGER)
RETURNS DATE
IMMUTABLE
LANGUAGE SQL AS $_$
SELECT v_first_day_of_month + ( 7 + $2 - v_day_of_week) % 7
        AS first_dow_of_month
FROM (
      SELECT v_first_day_of_month
             , extract('dow' from v_first_day_of_month)::integer
                             AS v_day_of_week
      FROM (SELECT date_trunc('month', $1)::date)
           AS mon(v_first_day_of_month)) as calc;
$_$;
COMMENT ON FUNCTION first_dow_of_month(DATE, INTEGER) IS
'first_dow_of_month(date, integer) returns the first occurrence of a
particular weekday in '
'a given month. The first argument supplies the month (as a date),
and the second '
'argument is the day of the week index as returned by extract(''dow'')';


CREATE OR REPLACE FUNCTION nth_dow_of_month (
        DATE -- date in target month
        , INTEGER -- day of week index
        , INTEGER -- zero-based ordinal day of week index,
                  -- e.g., 0 is first, 1 is second, -1 is last.
)
RETURNS DATE
IMMUTABLE
STRICT
LANGUAGE sql AS $_$
     SELECT CASE
         WHEN $3 >= 0 THEN
             first_dow_of_month($1, $2) + $3 * 7
         ELSE
             first_dow_of_month(($1 + interval '1 month')::date, $2)
+ $3 * 7
         END;
$_$;
COMMENT ON FUNCTION nth_dow_of_month(DATE, INTEGER, INTEGER) IS
'nth_dow_of_month(date, integer, integer) returns the nth occurrence
of a particular '
'weekday in a given month. The first argument supplies the month (as
a date). '
'The second argument supplies the day of the week index as returned
by extract(''dow''). '
'The third argument supplies the zero-based index of the desired
occurrence, '
'e.g. 0 indicates the first occurrence and 1 indicates the second. A
negative index will '
' count from the end of the month, i.e., -1 is the last occurrence,
-2 is the second to last '
'occurrence. No bounds checking is done to ensure that the returned
date is within the '
'specified month.';


SELECT current_date
        , nth_dow_of_month(current_date, 3, 0) as first_wed
        , nth_dow_of_month(current_date, 3, 1) as second_wed
        , nth_dow_of_month(current_date, 3, 2) as third_wed
        , nth_dow_of_month(current_date, 3, -2) as second_to_last_wed
        , nth_dow_of_month(current_date, 3, 4) as third_wed
        , nth_dow_of_month(current_date, 3, -1) as last_wed
        , nth_dow_of_month(current_date, 3, 8) as ninth_wed;
     date    | first_wed  | second_wed | third_wed  |
second_to_last_wed | third_wed  |  last_wed  | ninth_wed
------------+------------+------------+------------
+--------------------+------------+------------+------------
2007-07-03 | 2007-07-04 | 2007-07-11 | 2007-07-18 |
2007-07-18         | 2007-08-01 | 2007-07-25 | 2007-08-29
(1 row)

Hope this helps.

Michael Glaesemann
grzm seespotcode net

[1](http://archives.postgresql.org/pgsql-sql/2007-06/msg00017.php)