Re: Extract date from week

Jaime Casanova wrote:
> On 11/8/05, lucas@presserv.org <lucas@presserv.org> wrote:
> 
>>Hi
>>Looking the e-mail I remembered a question.
>>I saw that "select extract (week from now()::date)" will return the
>>week number
>>of current year. But, how can I convert a week to the first reference
>>date. Ex:
>>select extract(week from '20050105'::date);  -- 5 Jan 2005
>>--Returns--
>>date_part |
>>1         |
>>
>>It is the first week of year (2005), and how can I get what is the first date
>>references the week 1? Ex:
>>select ???? week 1
>>--should return---
>>date     |
>>20050103 |     -- 3 Jan 2005
>>
>>Thank you.
>>Lucas Vendramin
>>
>>
> 
> 
> Extracted from:
> http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
> 
> --- begin extracted text ---
> 
> week
> The number of the week of the year that the day is in. By definition
> (ISO 8601), the first week of a year contains January 4 of that year.
> (The ISO-8601 week starts on Monday.) In other words, the first
> Thursday of a year is in week 1 of that year. (for timestamp values
> only)
> 
> Because of this, it is possible for early January dates to be part of
> the 52nd or 53rd week of the previous year. For example, 2005-01-01 is
> part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd
> week of year 2005.
> 
> SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
> Result: 7
> 
> --- end extracted text ---
> 
> --
> regards,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 


Hi

http://www.postgresql.org/docs/8.0/static/functions-formatting.html

for the first week of 2005 use

=> select to_timestamp('1 2005','IW YYYY')::date as week_start; week_start
------------ 2005-01-03



Regards Neil.