{"id":2708,"date":"2018-06-25T15:02:05","date_gmt":"2018-06-25T22:02:05","guid":{"rendered":"https:\/\/database.guide\/?p=2708"},"modified":"2021-01-28T14:11:20","modified_gmt":"2021-01-28T04:11:20","slug":"dayofweek-examples-mysql","status":"publish","type":"post","link":"https:\/\/database.guide\/dayofweek-examples-mysql\/","title":{"rendered":"DAYOFWEEK() Examples – MySQL"},"content":{"rendered":"

You can use the DAYOFWEEK()<\/code> function in MySQL<\/a> to return the day of the week from a date.<\/p>\n

In this context, a return value of 1<\/em> corresponds to Sunday, 2<\/em> corresponds to Monday, etc.<\/p>\n

This article contains examples to demonstrate.<\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

DAYOFWEEK(date)\r\n<\/pre>\n
Where date<\/code> is the date value that you want the day of the week returned from.<\/p>\n
Example<\/h2>\n
Here’s an example to demonstrate.<\/p>\n
SELECT DAYOFWEEK('2019-01-01') AS 'Result';\r\n<\/pre>\n
Result:<\/p>\n
+--------+\r\n| Result |\r\n+--------+\r\n|      3 |\r\n+--------+\r\n<\/pre>\n
We can run this alongside the DAYNAME()<\/code><\/a> function to see which day it corresponds to:<\/p>\n
SET @date = '2019-01-01';\r\nSELECT \r\n    @date,\r\n    DAYNAME(@date) AS 'Day Name',\r\n    DAYOFWEEK(@date) AS 'Day of Week';\r\n<\/pre>\n
Result:<\/p>\n
+------------+----------+-------------+\r\n| @date      | Day Name | Day of Week |\r\n+------------+----------+-------------+\r\n| 2019-01-01 | Tuesday  |           3 |\r\n+------------+----------+-------------+\r\n<\/pre>\n
DAYOFWEEK() vs DAYOFMONTH()<\/h2>\n
Here’s an example to demonstrate the difference between the DAYOFWEEK()<\/code> and DAYOFMONTH()<\/code><\/a> functions.<\/p>\n
SET @date = '2019-01-20';\r\nSELECT \r\n    @date,\r\n    DAYNAME(@date) AS 'Day Name',\r\n    DAYOFWEEK(@date) AS 'Day of Week',\r\n    DAYOFMONTH(@date) AS 'Day of Month';\r\n<\/pre>\n
Result:<\/p>\n
+------------+----------+-------------+--------------+\r\n| @date      | Day Name | Day of Week | Day of Month |\r\n+------------+----------+-------------+--------------+\r\n| 2019-01-20 | Sunday   |           1 |           20 |\r\n+------------+----------+-------------+--------------+\r\n<\/pre>\n
The DAYOFMONTH()<\/code> function returns a value between 1<\/em> and 31<\/em> (or 0<\/em> for dates with a zero day part) that represents the day of the month. It resets back to 1<\/em> at the start of each month.<\/p>\n
The DAYOFWEEK()<\/code> function on the other hand, returns a value between 1<\/em> and 7<\/em>. It resets back to 1<\/em> at the start of each week.<\/p>\n
A Database Example<\/h2>\n
Here, we do a similar thing to the previous examples, except that this example uses data from a database:<\/p>\n
USE sakila;\r\nSELECT\r\n  payment_date AS 'Date\/Time',\r\n  DAYNAME(payment_date) AS 'Day Name',\r\n  DAYOFWEEK(payment_date) AS 'Day of Week'\r\nFROM payment\r\nWHERE payment_id = 1;\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+-----------+-------------+\r\n| Date\/Time           | Day Name  | Day of Week |\r\n+---------------------+-----------+-------------+\r\n| 2005-05-25 11:30:37 | Wednesday |           4 |\r\n+---------------------+-----------+-------------+\r\n<\/pre>\n
Current Date\/Time<\/h2>\n
Here’s an example of extracting the day part from the current date and time (which is returned using the NOW()<\/code><\/a> function).<\/p>\n
SELECT \r\n    NOW(),\r\n    DAYOFWEEK(NOW());\r\n<\/pre>\n
Result:<\/p>\n
+---------------------+------------------+\r\n| NOW()               | DAYOFWEEK(NOW()) |\r\n+---------------------+------------------+\r\n| 2018-06-26 07:42:31 |                3 |\r\n+---------------------+------------------+\r\n<\/pre>\n
Another way to do this is to use the CURDATE()<\/code><\/a> function, which returns only the date (but not the time).<\/p>\n
SELECT \r\n    CURDATE(),\r\n    DAYOFWEEK(CURDATE());   \r\n<\/pre>\n
Result:<\/p>\n
+------------+----------------------+\r\n| CURDATE()  | DAYOFWEEK(CURDATE()) |\r\n+------------+----------------------+\r\n| 2018-06-26 |                    3 |\r\n+------------+----------------------+\r\n<\/pre>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
You can use the DAYOFWEEK() function in MySQL to return the day of the week from a date. In this context, a return value of 1 corresponds to Sunday, 2 corresponds to Monday, etc. This article contains examples to demonstrate.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[68,115,93],"class_list":["post-2708","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-date-functions","tag-dates","tag-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2708","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2708"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2708\/revisions"}],"predecessor-version":[{"id":2713,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2708\/revisions\/2713"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2708"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2708"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2708"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}