Re: YearWeek() addition to documentation

[M. G.]

I'll try (monospaced text is my addition)

YEARWEEK(date), YEARWEEK(date,mode)
Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.

When used as an aggregate function YEARWEEK() results will not split across year boundaries:

mysql> CREATE TABLE Dated_items (Purchase_date DATE, Price DECIMAL(6,2));
mysql> INSERT INTO Dated_items Values
       ('2007-01-01',10.0),('2007-01-2', 20.0),
       ('2007-01-3', 15.0),('2007-01-4', 25.0),
       ('2007-01-5', 30.0),('2007-01-8', 50.0);
mysql> SELECT YearWeek(Purchase_Date) AS Week, SUM(Price) as Total
    ->        FROM dated_items GROUP BY Week;
+--------+--------+
| Week   | Total  |
+--------+--------+
| 200653 | 100.00 |
| 200701 |  50.00 |
+--------+--------+
2 rows in set (0.00 sec)

mysql> SELECT YEARWEEK('1987-01-01');
-> 198653

Note that the week number is different from what the WEEK() function would return (0) for optional arguments 0 or 1, as WEEK() then returns the week in the context of the given year.

Regards,
Meir



Edited 1 time(s). Last edit at 10/03/2009 01:09AM by Jon Stephens.