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.