MySQL WEEK Function

MySQL WEEK Function; Through this tutorial, i am going to show you how to use mysql WEEK() function with the help of examples.

MySQL WEEK() Function

The MySQL WEEK() is used to return the  the week number from a date. It will return value between 1 to 53.

Syntax of MySQL WEEK() Function

The WEEK() function syntax is:

 WEEK(date[,mode]) 

Here :

• The date is the date you want the week number to return.
• Mode is a number that specifies whether week should start on Sunday or Monday and weeks should be of 53 or 1 to 53. See the table below for possible mode values.

Example 1 – MySQL WEEK() Function

Let’s take first example of mysql week function; as follows:

SELECT WEEK('2020-06-18') AS 'Result';

Output-1

+--------+
| Result |
+--------+
|     24 |
+--------+

Example 2 – MySQL WEEK() Function

SELECT WEEK('2020-10-18') AS 'Result';

Output-2

+--------+
| Result |
+--------+
|     42 |
+--------+

Example 3 – MySQL WEEK() Function

To get different results depending on the mode you are using week function; as follows:

SET @date = '2019-07-12';
SELECT 
  WEEK(@date, 0) AS 'Mode 0',
  WEEK(@date, 1) AS 'Mode 1',
  WEEK(@date, 2) AS 'Mode 2',
  WEEK(@date, 3) AS 'Mode 3',
  WEEK(@date, 4) AS 'Mode 4',
  WEEK(@date, 5) AS 'Mode 5',
  WEEK(@date, 6) AS 'Mode 6',
  WEEK(@date, 7) AS 'Mode 7';

Output-3

+--------+--------+--------+--------+--------+--------+--------+--------+
| Mode 0 | Mode 1 | Mode 2 | Mode 3 | Mode 4 | Mode 5 | Mode 6 | Mode 7 |
+--------+--------+--------+--------+--------+--------+--------+--------+
|      0 |      1 |     52 |      1 |      1 |      0 |      1 |     53 |
+--------+--------+--------+--------+--------+--------+--------+--------+

Example 4 – MySQL WEEK() Function

To remove part of the day with a column when running the query against the database.

 SELECT
 created_at AS create_date,
 WEEK(created_at) AS week
 FROM users
 WHERE id= 112;

Output-4

+---------------------+--------------+
| create_date         |  week        |
+---------------------+--------------+
| 2010-08-23 10:33:39 |           34 |
+---------------------+--------------+

Example 5 – MySQL WEEK() Function

To extract part of the week number from the current date and time (which is now returned using the () function).

  SELECT 
  NOW(),
  WEEK(NOW());

Output-5

+---------------------+--------------------+
| NOW()               | WEEK(NOW())        |
+---------------------+--------------------+
| 2019-07-10 18:30:44 |         27         |
+---------------------+--------------------+

Example 6 – MySQL WEEK() Function

Let’s take a another example of using CURDATE() function. Basically CURDATE() function returns only the date without time.

SELECT 
CURDATE(),
WEEK(CURDATE());    

Output-6

+------------+-----------------------+
| CURDATE()  |    WEEK(CURDATE())    |
+------------+-----------------------+
| 2019-05-15 |             27        |
+------------+-----------------------+

Conclusion

MySQL week function tutorial, You have learned how to use mysql WEEK() function with various examples.

Recommended MySQL Tutorials