Return the Day, Month, and Year in MySQL

Posted on by Ian

MySQL has a bunch of different functions that enable us to get various date parts – such as the day, month, and year – from a date.

The DATE_FORMAT() Function

The DATE_FORMAT() function is great if you want to return the date parts all in a single field.

Example:

SELECT DATE_FORMAT('2035-12-19', '%W, %D %M %Y');

Result:

Wednesday, 19th December 2035

You can also return just the short day and month names if that’s the desired outcome:

SELECT DATE_FORMAT('2035-12-19', '%a, %D %b %Y');

Result:

Wed, 19th Dec 2035

Or you can return the day month numbers:

SELECT DATE_FORMAT('2035-12-19', '%d/%c/%Y');

Result:

19/12/2035

You can also return each date part in its own field, if required:

SELECT 
    DATE_FORMAT('2035-12-19', '%d') AS Day,
    DATE_FORMAT('2035-12-19', '%c') AS Month,
    DATE_FORMAT('2035-12-19', '%Y') AS Year;

Result:

+------+-------+------+
| Day  | Month | Year |
+------+-------+------+
| 19   | 12    | 2035 |
+------+-------+------+

See this full list of format specifiers for a full rundown on the format specifiers you can use to construct a format string.

DATE_FORMAT() also accepts an optional locale argument that you can use to specify the language of the day and month names. See MySQL DATE_FORMAT() Examples for more.

The EXTRACT() Function

The EXTRACT() function allows you to extract a specified unit from the date. Therefore, you can use it to extract the day, month, and year (as well as other units) from the date.

Example:

SELECT 
    EXTRACT(DAY FROM '2035-12-19') AS Day,
    EXTRACT(MONTH FROM '2035-12-19') AS Month,
    EXTRACT(YEAR FROM '2035-12-19') AS Year;

Result:

+------+-------+------+
| Day  | Month | Year |
+------+-------+------+
|   19 |    12 | 2035 |
+------+-------+------+

Functions for Returning a Specific Date Unit

MySQL also has some more specific functions that return a specific date unit.

Below is a list of the functions that return the day, month, and year from a date.

The DAYNAME() Function

MySQL has more than one function for returning the day. This is because there’s more than one way to represent the day. MySQL needs to know whether you want the day name, the day of the week number, the day of the month, day of year, etc.

The DAYNAME() function returns the weekday name:

SELECT DAYNAME('2035-12-19');

Result:

Wednesday

The DAYOFMONTH() Function

The DAYOFMONTH() function returns the day of the month number.

Example:

SELECT DAYOFMONTH('2035-12-19');

Result:

19

The DAY() Function

The DAY() function is actually a synonym for the DAYOFMONTH() function.

Example:

SELECT DAY('2035-12-19');

Result:

19

As expected, the same result as DAYOFMONTH().

The DAYOFWEEK() Function

The DAYOFWEEK() function returns the day of the week index for the date, as specified by the ODBC standard (1 = Sunday, 2 = Monday, …, 7 = Saturday).

Example:

SELECT DAYOFWEEK('2035-12-19');

Result:

4

See WEEKDAY() below for different indexing.

The WEEKDAY() Function

The WEEKDAY() function is similar to DAYOFWEEK() in that it returns the week index for the date. The difference is that it uses a different index numbering (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Example:

SELECT WEEKDAY('2035-12-19');

Result:

2

The DAYOFYEAR() Function

The DAYOFYEAR() function returns the day of the year for the date, in the range 1 to 366.

Example:

SELECT DAYOFYEAR('2035-12-19');

Result:

353

The MONTH() Function

The MONTH() function returns the month in the range 1 to 12 for January to December, or 0 for dates that have a zero month part (such as 0000-00-00).

Example:

SELECT MONTH('2035-12-19');

Result:

12

The MONTHNAME() Function

As the name suggests, the MONTHNAME() function returns the month name. The language used for the name is controlled by the value of the lc_time_names system variable.

Example:

SELECT MONTHNAME('2035-12-19');

Result:

December

The YEAR() Function

The YEAR() function returns the year part of the date.

Example:

SELECT YEAR('2035-12-19');

Result:

2035