Re: How can I get the first and last date of a week, based on the week number and the year - Mailing list pgsql-general

Bruno Baguette wrote:
> Hello !
>
> I have a week number (ISO 8601) and a year, based on theses values, I
> would like to get the first and the last dates of that week.

Broken down step-by-step. End of week left as an exercise

SELECT
   doy,
   EXTRACT(dow FROM doy) AS offset,
   (doy - EXTRACT(dow FROM doy) * '1 day'::interval)::date AS start_of_week
FROM
   (SELECT ('2008-01-04'::date + 8 * '1 week'::interval)::date AS doy)
AS foo;

     doy     | offset | start_of_week
------------+--------+---------------
  2008-02-29 |      5 | 2008-02-24
(1 row)

--
   Richard Huxton
   Archonet Ltd