Claudio Valderrama C. wrote:

>> -----Original Message-----
>> From: Firebird-Architect@yahoogroups.com
>> [mailto:Firebird-Architect@yahoogroups.com]On Behalf Of Daniel Rail
>>
>>
>>> Then you would also have to return the corresponding year. IMHO it's
>>> sufficient to cover such a higher level task by a sp (see
>>> http://www.danner-net.de/fbd_sql_timestamp_to_week_of_year.htm.
>>>
>> Why the year? Week number one is the week where January 1 is on. So
>> if December 31 is on the same week as January 1, then the week number
>> for December 31 is one.
>>
>
> Isn't that the first week is the one that has a Thursday? Anything that
> doesn't meet that property is the last week of the previous year.
>
> C.
>
>
>

-------- Original Message --------
Subject: Re: [Firebird-devel] Weeks in a year
Date: Tue, 18 Oct 2005 02:59:17 -0200
From: Alexandre Benson Smith <iblist@...>
Reply-To: firebird-devel@...
To: firebird-devel@...
References: <NGBBLMEJHKCJJCALGNKJAEHNEBAA.cvalde@...>
<43547ED0.9030204@...>


ISO week number
Week number 01 to 53 of the year this date falls in, according to
the rules of ISO standard IS-8601 section 5.5. A week that lies
partly in one year and partly in another is assigned a number in the
year in which most of its days lie. This means that week 1 of any
year is the week that contains 4 January, or equivalently week 1 of
any year is the week that contains the first Thursday in January.
Most years have 52 weeks, but years that start on a Thursday and
leap years that start on a Wednesday have 53 weeks. January 1 may
well be in week 53 of the previous year!. ISO specifies a lead 0 to
create a 2-digit year. Java does not do that unless you code the
display specially.

http://mindprod.com/jgloss/calendar.html




ISO Week Numbers

The International Organization for Standardisation, based in
Switzerland, issued Standard 8601 -- Representation Of Dates And Times,
in 1988. This provides some standardization for "week numbers". Of
course, compliance with these standards is entirely voluntary, so your
business may or may not use the ISO definitions.

Under the ISO standard, a week always begins on a Monday, and ends on a
Sunday. The first week of a year is that week which contains the first
Thursday of the year, or, equivalently, contains Jan-4.

While this provides some standardization, it can lead to unexpected
results -- namely that the first few days of a year may not be in week 1
at all. Instead, they will be in week 52 of the preceding year! For
example, the year 2000 began on Saturday. Under the ISO standard, weeks
always begin on a Monday. In 2000, the first Thursday was Jan-6, so
week 1 begins the preceding Monday, or Jan-3. Therefore, the first two
days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard,
week 1 will always have at least 4 days. If 1-Jan falls on a Friday,
Saturday, or Sunday, the first few days of the year are defined as
being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For
example, the year 2000 does not have a week 53. Week 52 begins on
Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does
have a week 53, from Monday, 27-Dec , through Friday, 31-Dec.

To compute the ISO week number of a date in cell A1, use the following
formula, provided by Laurent Longre. Note that this formula will not
always return the correct value if you are using the 1904-based date
system.

=INT((A1-SUM(MOD(DATE(YEAR(A1-MOD(A1-2,7)+3),1,2),
{1E+99,7})*{1,-1})+5)/7)*

The following formula, developed by Evert van den Heuvel, works with
either the 1900 or 1904 based date system.

* =1+INT((A1-DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,5)+
WEEKDAY(DATE(YEAR(A1+4-WEEKDAY(A1+6)),1,3)))/7)

http://www.cpearson.com/excel/weeknum.htm

and weekpedia
http://en.wikipedia.org/wiki/ISO_8601

HTH


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br