<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Courier New, Courier, monospace">Hello,<br>
<br>
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? <br>
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. <br>
<br>
Thanks<br>
<br>
Irek.<br>
<br>
CREATE OR REPLACE FUNCTION week2date(double precision, double
precision) RETURNS SETOF date<br>
AS<br>
$_$<br>
SELECT day<br>
FROM (<br>
SELECT to_char(day, 'IYYY')::integer AS iyyy,<br>
to_char(day, 'IW' )::integer AS iw,<br>
day<br>
FROM (<br>
SELECT start + generate_series(0, n) AS day<br>
FROM (<br>
SELECT start,<br>
(stop - start)::integer AS n<br>
FROM (<br>
SELECT (to_date($1::text, 'YYYY'::text) -
interval '3 days')::date AS start,<br>
(to_date($1::text, 'YYYY'::text) + interval '1
year 3 days')::date AS stop<br>
) ss<br>
) aa<br>
) bb<br>
) cc<br>
WHERE iw = $2 AND iyyy = $1<br>
ORDER<br>
BY day<br>
$_$<br>
LANGUAGE SQL<br>
IMMUTABLE<br>
;<br>
<br>
SELECT week2date(date_part('year', now()), date_part('week', now()));<br>
week2date<br>
------------<br>
2010-03-22<br>
2010-03-23<br>
2010-03-24<br>
2010-03-25<br>
2010-03-26<br>
2010-03-27<br>
2010-03-28<br>
(7 rows)<br>
<br>
SELECT week2date(2009, 53);<br>
week2date<br>
------------<br>
2009-12-28<br>
2009-12-29<br>
2009-12-30<br>
2009-12-31<br>
2010-01-01<br>
2010-01-02<br>
2010-01-03<br>
(7 rows)<br>
<br>
SELECT week2date(2010, 1);<br>
week2date<br>
------------<br>
2010-01-04<br>
2010-01-05<br>
2010-01-06<br>
2010-01-07<br>
2010-01-08<br>
2010-01-09<br>
2010-01-10<br>
(7 rows)<br>
<br>
</font>
</body>
</html>