MySQL DAYOFWEEK, DAYOFMONTH, DAYOFYEAR

Week Month Year

In this tutorial, we will learn about the MySQL DAYOFWEEK(), DAYOFMONTH() and DAYOFYEAR() functions. Suppose you have a date and you want to know its weekday number. What if you also want to know the day of the month number for this date? Furthermore, you want to know what day number is it in the year for this date? For these operations, MySQL provides us with the DAYOFWEEK(), DAYOFMONTH() and DAYOFYEAR() functions respectively.

  • The DAYOFWEEK() function is used to return the weekday number (from 1 to 7) of the specified date. Note that the WEEKDAY() function is used to return the weekday index (from 0 to 6) as opposed to DAYOFWEEK() which returns the weekday number (from 1 to 7). The weekday number values are – 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, and 7=Saturday.
  • The DAYOFMONTH() function is used to return the month number(from 1 to 31) from the given date. Note that this function is equal to the DAY() function.
  • The DAYOFYEAR() function is used to return the day number in the year for the given date (from 1 to 366). 

Syntax of MySQL DAYOFWEEK()

DAYOFWEEK(date)

Where ‘date’ is the date from which you want to know the weekday number.


Syntax of MySQL DAYOFMONTH()

DAYOFMONTH(date)

Where ‘date’ is the date from which you want to know the day number in the month.


Syntax of MySQL DAYOFYEAR()

DAYOFYEAR(date)

Where ‘date’ is the date from which you want to know the day number in the year.


Examples of MySQL DAYOFWEEK()

Let us start with a basic example. Let us find out the weekday number of the dates – 2021-01-15 and 2021-02-10. Let us write a query for this using the SELECT statement and an alias

SELECT DAYOFWEEK('2021-01-15') AS DayOfTheWeek; 
SELECT DAYOFWEEK('2021-02-10') AS DayOfTheWeek;

And the output is – 

MySQL Dayofweek Basic Example

MySQL DAYOFWEEK() With Datetime Value

We can also pass a datetime value as a parameter to the MySQL DAYOFWEEK() function. Let us see this using the below example.

SELECT DAYOFWEEK('2020-12-31 12:59:02') AS DayOfTheWeek;

And the output is – 

MySQL Dayofweek Datetime

MySQL DAYOFWEEK() With Wrong Date Value

Just like other date functions, DAYOFWEEK() returns NULL if we pass a wrong date value (day greater than 31 or month greater than 12). Let us see the below example demonstrating this – 

SELECT DAYOFWEEK('2021-02-120') AS DayOfTheWeek;

And the output is – 

Dayofweek Wrongdate

Examples of MySQL DAYOFMONTH()

Let us start with a basic example. Let us find out the day number in the month of the dates – 2021-02-15 and 2021-02-5. Let us write a query for this using the SELECT statement and an alias

SELECT DAYOFMONTH('2021-02-15') AS DayOfTheMonth; 
SELECT DAYOFMONTH('2021-02-5') AS DayOfTheMonth;

And the output is – 

MySQL Dayofmonth Basic Example

MySQL DAYOFMONTH() With Datetime Value

We can also pass a datetime value as a parameter to the MySQL DAYOFMONTH() function. Let us see this using the below example.

SELECT DAYOFMONTH('2021-02-14 15:23:44') AS DayOfTheMonth;

And the output is – 

MySQL Dayofmonth Datetime

MySQL DAYOFMONTH() With Wrong Date Value

Just like other date functions, MySQL DAYOFMONTH() returns NULL if we pass a wrong date value (day greater than 31 or month greater than 12). Let us see the below example demonstrating this – 

SELECT DAYOFMONTH('2021-02-45') AS DayOfTheMonth;

And the output is – 

MySQL Dayofmonth Wrongdate

Examples of MySQL DAYOFYEAR()

Let us start with a basic example. Let us find out the day number in the year of the dates – 2021-02-15 and 2020-11-07. Let us write a query for this using the SELECT statement and an alias. 

SELECT DAYOFYEAR('2021-02-15') AS DayOfTheYear; 
SELECT DAYOFYEAR('2020-11-07') AS DayOfTheYear;

And the output is – 

MySQL Dayofyear Basic Example

MySQL DAYOFYEAR() With Datetime Value

We can also pass a datetime value as a parameter to the MySQL DAYOFYEAR() function. Let us see this using the below example.

SELECT DAYOFYEAR('2020-08-16 16:45:52') AS DayOfTheYear;

And the output is – 

Dayofyear Datetime

MySQL DAYOFYEAR() With Wrong Date Value

Just like other date functions, MySQL DAYOFYEAR() returns NULL if we pass a wrong date value (day greater than 31 or month greater than 12). Let us see the below example demonstrating this – 

SELECT DAYOFYEAR('2020-45-07') AS DayOfTheYear;

And the output is – 

Dayofyear Wrongdate

DAYOFWEEK vs DAYOFMONTH vs DAYOFYEAR

Let us see an example in which we use all the three functions and find out the weekday number, the day number in the month and the day number of the year of the present date. We will use the CURDATE() function for this. The query is – 

SELECT DAYOFWEEK(CURDATE()) AS DayOfTheWeek, 
DAYOFMONTH(CURDATE()) AS DayOfTheMonth, 
DAYOFYEAR(CURDATE()) AS DayOfTheYear;

And the output is – 

Dayofweek Dayofmonth Dayofyear

Working With Tables

Consider the below Employee table.

Weekday Employee Table
Employee Table

Building on the last example, let us display the employee id, employee name, their joining date and the weekday number, the day number in the month and the day number of the year of the date they joined. The query is – 

SELECT eid, Name, Date_Joined, 
DAYOFWEEK(Date_Joined) AS DayOfTheWeek, 
DAYOFMONTH(Date_Joined) AS DayOfTheMonth, 
DAYOFYEAR(Date_Joined) AS DayOfTheYear 
FROM Employee;

And the output is – 

Week Month Year Tableexample1

Conclusion

The above functions are widely used while dealing with date and datetime values. I would encourage you to practice different examples with different MySQL clauses.


References