Re: converting a specified year and week into a date

am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:
>
> hi guys,
> i was just wondering if it was at all possible to turn a year and a given
> week number into a real date just using postgresql commands?
>
>
> e.g. if i have year = 2004 and week = 1,
> can i turn that into say 2004-01-01 (so that the specified
> date is the one for the beginning of week 1 in the year 2004

I have found this little function, not realy what you want but trivial to
adapt to your problem:
(it returns a string with first and last day of the week)

create or replace function get_week(IN jahr int, IN kw int) returns text as $$
declare
datum date;
ret text;
begin
datum = (jahr || '-01-01')::date;

loop
exit when extract(dow from datum) = 4;
datum = datum + '1day'::interval;
end loop;
ret = to_char(datum+(7*(kw-1)-3||'days')::interval,'dd-mm-yyyy') || ' - ' || to_char(datum+(3+7*(kw-1)||'days')::interval,'dd-mm-yyyy');
return ret;
end;
$$ language plpgsql immutable strict;

test=*# select get_week(2007,2);
get_week
-------------------------
08-01-2007 - 14-01-2007
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net