MySQL WEEK Function

MySQL WEEK Function; In this tutorial, wou will learn how to use mysql WEEK() function with the help of examples.

When we need to get the week number for a given date in mysql query that time we could use to mysql WEEK() function. and it will return value between 1 to 53. We will take some example of with mysql various function like, CURDATE(), NOW() etc.

MySQL WEEK() Function

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

Syntax

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

Now we take an example to demonstrate.

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

Output-1

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

Example-2

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

Output-2

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

Example-3 | Comparison of modes

Here’s a we will comparison of how you can get different results depending on the mode you are using.

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 | Database Example

Next database example, we take an example of removing 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 | Current Date/Time

Let’s take another example , extracting 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 | CURDATE() 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

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