[SQL] Extract week from date, start with sunday

[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?

Thanks!


Kind Regards,
Robert



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Extract week from date, start with sunday

[A. Kretschmer]

am  07.11.2005, um 14:47:35 +0100 mailte Robert Blixt folgendes:
> 
>  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?

Add 1 day to ldate.

test=# select to_char('2005-11-05'::date+'1 day'::interval, 'IW');
 to_char
-
 44
(1 row)

test=# select to_char('2005-11-06'::date+'1 day'::interval, 'IW');
 to_char
-
 45
(1 row)


HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47212,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Extract week from date, start with sunday

[Robert Blixt]

> -Ursprungligt meddelande-
> Från: Janning Vygen [mailto:[EMAIL PROTECTED]
> Skickat: den 7 november 2005 15:18
> Till: pgsql-sql@postgresql.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



---(end of broadcast)---
TIP 6: explain analyze is your friend