WEEK MySQL Function Examples

WEEK MySQL Function Examples

Here You will learn how to use mysql WEEK() function with syntax and examples. Today we are going to discuss about MySQL WEEK() function with various 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.

You may like

  1. WEEKOFYEAR() Function Examples – MySQL
  2. WEEKDAY() Function Examples – MySQL
  3. LAST_DAY MySQL Function Examples
  4. MySQL – DAYOFWEEK() Function Examples
  5. DAYOFYEAR() Function Examples – MySQL
  6. MySQL YEARWEEK() Function Examples – MySQL
  7. MySQL weekday function with example
  8. MySQL CURRENT TIMESTAMP Function Examples
  9. Get month name from date in MySQL
  10. mysql get first day of the current month
  11. mysql last day of previous month
  12. Get month number from month name in mysql
  13. mysql get day of week number from date
  14. Mysql Get Data Of Current Date, Week, Month, YEAR
  15. Query For Get Data Of Last Day, Week, Month, YEAR – Mysql

If you have any questions or thoughts to share, use the comment form below to reach us.

Leave a Reply

Your email address will not be published. Required fields are marked *