Re: Extract week from date, start with sunday

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.