On Tue, 2006-08-01 at 14:59 -0400, John Hostage wrote:
> In fact, starting the week on Sunday seems to be a convention only in
> the US. The international standard is to start on Monday
> <http://en.wikipedia.org/wiki/ISO_week_date>
> I'm not familiar with what OOo does, but it seems to me it should follow
> the standard. Week numbers aren't even used in the US that I'm aware of.
>
> John Hostage
>
> Manfred Moser wrote:
> > Not correct. That is internationally different and there should be a
> > setting for your database whichever you use.
> >
> > manfred
> >
> > Joe Conner wrote:
> >> Weeks DO begin on Sunday. Check your calendar.
> >>
> >> Dmitri Pissarenko wrote:
> >>> Hello!
> >>>
> >>> I have a table with several fields, one of them is a date field.
> >>>
> >>> I want to create a query, which would tell me the week number for
> >>> every date. For this purpose I use the following query:
> >>>
> >>> SELECT WEEK(date) AS "Week", date
> >>> FROM myTable
> >>>
> >>> This works, however the week number calculation assumes that the week
> >>> starts on Sunday and ends on Saturday.
> >>>
> >>> I want the week to start on Monday and end on Sunday.
> >>>
> >>> How should I change the query so that the week number is calculated
> >>> for weeks starting on Monday?
> >>>
> >>> TIA
> >>>
> >>> Dmitri Pissarenko
For my Locale in the UK, Week() starts from Monday, but the following
should work OK, if Sunday is your first day.
SELECT CASEWHEN(DAYOFWEEK("date")=1,WEEK("date")-1,WEEK("date")) AS "Week",
"date"
FROM myTable
The built in Functions are listed here
http://www.hsqldb.org/web/hsqlDocsFrame.html
HTH
Barrie
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
