Date: Tue, 11 Oct 2011 09:19:48 -0700 (PDT)
From: skdangi <skdangi@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1318349988812-4892306.post@n5.nabble.com>
In-Reply-To: <20051107140036.11DC6D6873@svr1.postgresql.org>
References: <20051107140036.11DC6D6873@svr1.postgresql.org>
Subject: Re: Extract week from date, start with sunday
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

You can use the below query to get the week from Sunday. 

select ceil(((now()::date -((CAST(EXTRACT (year FROM  now()) as
text)||'-01-04')::date - CAST
				(EXTRACT (isodow FROM  (CAST(EXTRACT (year FROM  now()) as
text)||'-01-04')::date)
				as integer)
				))+1)/7.0)


While using it you need to replace the now() with the date value you want to
compute the week number.

Explanation: 
1)(no of days between the date provided and first day of first week of the
year +1)/7.0
First week of the year contains 4th January (refer Note).
So first day of first week = first day of the week in which 4th January
belongs.
2)Finally take the ceiling of the result.


Note: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. 

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Extract-week-from-date-start-with-sunday-tp2144144p4892306.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.