Re: Week in year before 1998 starts from 1 instead of 0

[Aldrian Gintingsuka]

I might be a little unclear about my problem. What I am confused about is why
the WEEK command returns 0 for Jan 1 of some years and returns 1 for some
other years.

WEEK('1997-01-01') => 1;
WEEK('1998-01-01') => 0;

I received a direct response from Piet Wesselman, and (with his his
permission) would like to share it here in the list.

>On Mon, 26 Apr 1999, Piet Wesselman wrote:
>
>pwessel> January 1 is only in week 1, if this week contains the first Thursday
>pwessel> of the year (or January 4, which is the same). See the ISO 8601:1988
>pwessel> standard for more information. 
>pwessel> Maybe this helps.
>pwessel> 
>pwessel> Piet Wesselman
>pwessel> 

And after testing some other years, I think I begin to understand how it
works.

  Jan 1 is in week 1 if that week contains at least four days (more than the
  number of days in the previous year's last week).

    If Sunday is the 1st day of the week,
    Jan 1 is in week 1 if it starts in (Sun, Mon, Tue, Wed)

    If Monday is the 1st day of the week,
    Jan 1 is in week 1 if it starts in (Mon, Tue, Wed, Thu)

    Otherwise, it is in week 0 (continuation of the last week of the previous
    year).

Is that how weeks are calculated in MySQL???

========================================================================
Aldrian Gintingsuka -- Systems Consultant -- Mitra Integrasi Informatika
Phone: [phone] ext. 1414 -- Fax: [phone]
Email: aldrian@stripped, aldrian@stripped
------------------------------------------------------------------------
Si tu es contre Dieu, tu es contre l'homme (Enigma: Sadeness part I)



On Mon, 26 Apr 1999, Michael Widenius wrote:

monty> 
monty> This is also ok. 1997.01.01 is on the first week of 1997 (check your
monty> calender).  I don't think that WEEK('1997-12-31') should return 1
monty> because this would also be very confusing and could lead to much
monty> bigger problems than '53' when you present things for example with
monty> GROUP BY..
monty> 
monty> The only 'right' thing that I can think of, would be to introduce a
monty> 'full_week()' function that returned the week in the 'YYYY-WW' format.
monty>