Re: Extract week from date, start with sunday

> -----Ursprungligt meddelande-----
> Från: Janning Vygen [mailto:vygen(at)planwerk6(dot)de]
> Skickat: den 7 november 2005 15:18
> Till: pgsql-sql(at)postgresql(dot)org
> Kopia: Robert Blixt
> Ämne: Re: [SQL] Extract week from date, start with sunday
>
> Am Montag, 7. November 2005 14:47 schrieb Robert Blixt:
> > Hi,
> >
> >
> > I am currently extracting my weeks from a date like this..
> >
> > SELECT INTO extractweek EXTRACT( week FROM ldate );
> >
> > This works fine, except that this assumes (according
> > to ISO standard) that weeks starts with Monday.
> >
> > I need the weeks to start with Sunday instead, otherwise
> > I will not get the correct week.
> >
> > Is there a workaround for this?
>
> calculation of weeks is not easy, but it should work like this to get a
> non-ISO week number:
>
> SELECT INTO extractweek EXTRACT( week FROM (ldate + '1 day'::interval));
>
> But its is just a guess. Because i dont know anything about your non-ISO
> rules. In which week is 2006-01-01? ISO Week 52/2005. Why dont you just
> take
> ISO Weeks. It's a standard. That's a good thing.
>
> kind regards
> Janning
>
> --
> PLANWERK 6 websolutions
> Herzogstraße 85, 40215 Düsseldorf
> Tel.: 0211-6015919 Fax: 0211-6015917
> http://www.planwerk6.de/

Thanks everyone for your help, adding a day
to the date does seem to do the trick.

I would certainly prefer to use the ISO standard.
However, the business sector that we develop for
use their standard weeks from Sun - Sat, so it isn't
really an option for us.

Thanks again everyone.

Kind Regards,
Robert