From: skdangi <skdangi@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Extract week from date, start with sunday
Date: Tue, 11 Oct 2011 09:19:48 -0700 (PDT)
Message-ID: <1318349988812-4892306.post@n5.nabble.com> (raw)
In-Reply-To: <20051107140036.11DC6D6873@svr1.postgresql.org>
References: <20051107140036.11DC6D6873@svr1.postgresql.org>
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.h...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: skdangi@gmail.com
Subject: Re: Extract week from date, start with sunday
In-Reply-To: <1318349988812-4892306.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox