[SQL] Week to date function

Thu, 25 Mar 2010 02:27:02 -0700

Hello,

is there any standard function, or a concise solution based on set of them, returning a set of dates included in a week of given year and week number?
I ended up with creating my own function as in the example below, but I am curious if I am not opening an open door.

Thanks

Irek.

CREATE OR REPLACE FUNCTION week2date(double precision, double precision) RETURNS SETOF date
AS
$_$
SELECT  day
FROM    (
    SELECT  to_char(day, 'IYYY')::integer    AS  iyyy,
            to_char(day, 'IW'  )::integer    AS  iw,
            day
    FROM    (
        SELECT  start + generate_series(0, n)   AS  day
        FROM    (
            SELECT  start,
                    (stop - start)::integer AS  n
            FROM    (
                SELECT  (to_date($1::text, 'YYYY'::text) - interval        '3 days')::date AS   start,
                        (to_date($1::text, 'YYYY'::text) + interval '1 year 3 days')::date AS   stop
            )       ss
        )       aa
    )       bb
)       cc
WHERE   iw = $2 AND iyyy = $1
ORDER
BY      day
$_$
LANGUAGE SQL
IMMUTABLE
;

SELECT week2date(date_part('year', now()), date_part('week', now()));
 week2date
------------
 2010-03-22
 2010-03-23
 2010-03-24
 2010-03-25
 2010-03-26
 2010-03-27
 2010-03-28
(7 rows)

SELECT week2date(2009, 53);
 week2date
------------
 2009-12-28
 2009-12-29
 2009-12-30
 2009-12-31
 2010-01-01
 2010-01-02
 2010-01-03
(7 rows)

SELECT week2date(2010,  1);
 week2date
------------
 2010-01-04
 2010-01-05
 2010-01-06
 2010-01-07
 2010-01-08
 2010-01-09
 2010-01-10
(7 rows)

Reply via email to