MySQL WEEK() function

WEEK() function

MySQL WEEK() returns the week number for a given date.

The argument allows the user to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If no argument is included with the function, it returns the default week format.

This function is useful in -

• WEEK() allows you to organize and report data on a weekly basis, which can be useful for business reporting and analysis.
• In business analytics, WEEK() is used to calculate various performance metrics on a weekly basis.
• It's useful for filtering records or data based on a specific week or range of weeks.
• It's used in resource allocation and workforce management systems to allocate resources on a weekly basis.
• In financial planning, WEEK() helps in budgeting and forecasting on a weekly time frame.
• WEEK() can be used in applications for scheduling and planning tasks or events on a weekly schedule.
• The function is used to group data by weeks, enabling you to see trends or patterns over time.

Syntax:

WEEK(date[,mode]);

Arguments:

Syntax Diagram:

MySQL Version: 8.0

The following table describes how the mode arguments work:

Pictorial Presentation:

Example: MySQL WEEK() function

The following statement will return the week of the specified date 2009-05-18.

Code:

SELECT WEEK('2009-05-18');

Output:

mysql> SELECT WEEK('2009-05-18');
+--------------------+
| WEEK('2009-05-18') |
+--------------------+
|                 20 | 
+--------------------+
1 row in set (0.02 sec)

Example: WEEK() function with day index

The following statement will return the week of the specified date 2009-05-18. 1 as the second argument defines that the first day of the week is assumed as Monday.

Code:

SELECT WEEK('2009-05-18',1);

Output:

mysql> SELECT WEEK('2009-05-18',1);
+----------------------+
| WEEK('2009-05-18',1) |
+----------------------+
|                   21 | 
+----------------------+
1 row in set (0.00 sec)

Example: WEEK() function using on table

The following statement will return name of the publishers, their date of establishment, the week in which it was established as WEEK(estd), the week in which it was established as WEEK(estd,1) assuming that the week started from Monday and the week in which it was established as WEEK(estd,2) assuming that the week started from Sunday.

Sample table: publisher

Code:

SELECT pub_name,estd,WEEK(estd),
   WEEK(estd,1),WEEK(estd,2)
    FROM publisher;

Output:

mysql> SELECT pub_name,estd,WEEK(estd),
    ->    WEEK(estd,1),WEEK(estd,2)
    ->     FROM publisher;
+------------------------------+------------+------------+--------------+--------------+
| pub_name                     | estd       | WEEK(estd) | WEEK(estd,1) | WEEK(estd,2) |
+------------------------------+------------+------------+--------------+--------------+
| Jex Max Publication          | 1969-12-25 |         51 |           52 |           51 | 
| BPP Publication              | 1985-10-01 |         39 |           40 |           39 | 
| New Harrold Publication      | 1975-09-05 |         35 |           36 |           35 | 
| Ultra Press Inc.             | 1948-07-10 |         27 |           28 |           27 | 
| Mountain Publication         | 1975-01-01 |          0 |            1 |           52 | 
| Summer Night Publication     | 1990-12-10 |         49 |           50 |           49 | 
| Pieterson Grp. of Publishers | 1950-07-15 |         28 |           28 |           28 | 
| Novel Publisher Ltd.         | 2000-01-01 |          0 |            0 |           52 | 
+------------------------------+------------+------------+--------------+--------------+
8 rows in set (0.17 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: UTC_TIMESTAMP()
Next: WEEKDAY()