Hi!

Mark> currently: Mark> select WEEK('2000-1-1', 0) -> 0 Mark> select WEEK('2000-1-1', 1) -> 0 Mark> select WEEK('2001-1-1', 0) -> 1 Mark> select WEEK('2001-1-1', 1) -> 1

Mark> however, the first day of 2001 is monday. Mark> so it should be that WEEK('2001-1-1',0) returns 0

Mark> of the 4 cases above, i believe that one of them is incorrect, which is
that WEEK(2001-1-1,0) Mark> should be 0 (that is, it should be the same as year 2000).

Mark> if i understand the definition of WEEK, then: Mark> if second arg is 0: it should return 0 for the 0-6 days preceding the
first sunday in the year. Mark> if second arg is 1: it should return 0 for the 0-6 days preceding the
first monday in the year.

The above is not correct. The idea the second argument to WEEK() should be 0 in countries (like USA) where the the week starts with Sunday and the second argument is 1 for countries (like Finland) where the week starts with Monday.

A more precise rule is:

If second arg is 0: It should return 0 for days XXXX-01-01 - XXXX-01-04 if Sunday-Wednesday is part of the previous year.

If second arg is 1: It should return 0 for days XXXX-01-01 - XXXX-01-04 if Monday-Thursday is part of the previous year.

Mark> In the year 2001, there are 6 days preceding the first Mark> sunday, so all 6 of them should be week 0 when the second arg is 0.

Do you in USA really count the days 2001-01-01 - 2001-01-06 to belong to the last week of 2000 ?

<cut>

Mark> btw, ISO 8601 defines week number 1 to be the week containing january 4
(i.e. the week Mark> containing the first thursday), and weeks start with monday.

This is what you get if you specify 1 as the last argument to WEEK()

Mark> Days that would be in "week 0" are instead assigned to the previous year;
week numbers Mark> are always in the range 1-53, not 0-53.

The reason MySQL returns 0 instead of '52' or '53' is for practical reasons. If you in your program use WEEK() on a date, it's VERY likely that it will be confused if MySQL would return(53) for a day in January.

For example week("2000-01-01") = 53

In this case it's very likely that the program would print to the user that this is year '2000' and week '52'.

If you want to have it totally correct, you should instead use: yearweek("2000-01-01") which returns '199952'.

Mark> It would be nice if you supported that as yet another definition Mark> (say, 2nd arg = 2) because some software packages use that (including the
C library Mark> in strftime). See many resources, such as
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Using '1' will get what you want.

Regards, Monty