MySQL DAYOFWEEK, DAYOFMONTH, DAYOFYEAR

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)Code language: SQL (Structured Query Language) (sql)

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

Syntax of MySQL DAYOFMONTH()

DAYOFMONTH(date)Code language: SQL (Structured Query Language) (sql)

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

Syntax of MySQL DAYOFYEAR()

DAYOFYEAR(date)Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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;Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below 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;Code language: SQL (Structured Query Language) (sql)

And the output is –

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

MySQL Official Documentation on DAYOFWEEK().
MySQL Official Documentation on DAYOFMONTH().
MySQL Official Documentation on DAYOFYEAR().