Hi

I get a strange result around the year end for the following SQL (DBISam
4.25):-

Select
 EXTRACT(YEAR FROM TheDate) As MyYear,
 EXTRACT(WEEK FROM TheDate) As MyWeek,
 TheDate
from
 mytable


Results
MyYear MyWeek    TheDate
2007     52               28/12/2007
2007     1                 31/12/2007

I'm confused!

Thanks

Mike

mike,

> Results
> MyYear MyWeek    TheDate
> 2007     52               28/12/2007
> 2007     1                 31/12/2007
>
> I'm confused!


Weeks start on Mondays and according to ISO 8601 standards "Week 1" is
is the week that contains the first Thursday in the year (among other
definitions). December 31st 2007 is a Monday and the following Thursday
is Jan 3rd. 2008 - ergo December 31st 2007 is the start date of "week 1
2008".

Eryk

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

Hi Eryk

I can understand it being week 1 of 2008 but the year returned is actually
2007...

Thanks

Mike

"Eryk Bottomley" <no@way.com> wrote in message
news:03F4D680-6EF2-4BEF-85E3-DFD057543402@news.elevatesoft.com...
> mike,
>
>> Results
>> MyYear MyWeek    TheDate
>> 2007     52               28/12/2007
>> 2007     1                 31/12/2007
>>
>> I'm confused!
>
>
> Weeks start on Mondays and according to ISO 8601 standards "Week 1" is is
> the week that contains the first Thursday in the year (among other
> definitions). December 31st 2007 is a Monday and the following Thursday is
> Jan 3rd. 2008 - ergo December 31st 2007 is the start date of "week 1
> 2008".
>
> Eryk
>
> http://en.wikipedia.org/wiki/ISO_8601

mike,

> I can understand it being week 1 of 2008 but the year returned is actually
> 2007...

Correct, because the year component of a date (YYYY-MM-DD) is always a
simple truncation to the first four digits. The two extract functions in
the query are unrelated to each other so they return two independently
correct answers. To get the result I presume you want you have to
extract the year component from the Thursday immediately following the
input date (weeks start on Mondays but week numbers 'count' Thursdays).

Eryk

OK. Got it.
Thanks.

Mike


> Correct, because the year component of a date (YYYY-MM-DD) is always a
> simple truncation to the first four digits. The two extract functions in
> the query are unrelated to each other so they return two independently
> correct answers. To get the result I presume you want you have to extract
> the year component from the Thursday immediately following the input date
> (weeks start on Mondays but week numbers 'count' Thursdays).
>
> Eryk