MySQL 8 how to select day, month and year from date

Or how to format the date into different output templates. In MySQL if you want to format date in order to extract only day, month or year you have several different options like:

• Date format in MySQL Using EXTRACT function
• Date format in MySQL Using DATE_FORMAT
• Date format in MySQL USING DAY, MONTH, YEAR
• MySQL where clause filter by DAY, MONTH, YEAR

This functions are not fully standard so if you want to use them in another RDMBS you will need to do a research for the specific dialect. They are tested with MySQL 5.7 and MySQL 8

Using EXTRACT function in MySQL

This function is easy to use and it can be applied for month and year format:

SELECT  EXTRACT( DAY FROM `mydate` ) as 'day' , EXTRACT( MONTH FROM `mydate` ) as 'month' FROM mytable;

result:

SELECT  EXTRACT( YEAR_MONTH FROM `mydate` )  FROM mytable;

result:

year|month
----|------filter
2016|5
2017|11

Below you can find most used units:

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• HOUR_MINUTE
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

Using DATE_FORMAT in MySQL

DATE_FORMAT is giving you more freedom and ways to customize the date format. This one is preferable if you need more fancy output like:

Example Day Month

SELECT DATE_FORMAT(mydate,'%d-%m')  FROM mytable;

result:

DATE_FORMAT(mydate,'%d-%m')
-----------|
19-05
1-11

Example Day Month Short Year

SELECT DATE_FORMAT(mydate,'%d/%m/%y')  FROM mytable;

result:

DATE_FORMAT(mydate,'%d/%m/%y')
-----------|
19/05/16
23/05/16

Example Year Text Month Day

SELECT DATE_FORMAT(mydate,'%Y-%M-%d')  FROM mytable;

result:

DATE_FORMAT(created_at,'%Y%m')
-----------|
2016-May-19
2016-May-23

Most frequent formats:

• %d Day of the month as a numeric value (01 to 31)
• %H Hour (00 to 23)
• %i Minutes (00 to 59)
• %j Day of the year (001 to 366)
• %M Month name in full (January to December)
• %m Month name as a numeric value (00 to 12)
• %p AM or PM
• %s Seconds (00 to 59)
• %W Weekday name in full (Sunday to Saturday)
• %w Day of the week where Sunday=0 and Saturday=6
• %Y Year as a numeric, 4-digit value
• %y Year as a numeric, 2-digit value

Date format in MySQL USING DAY, MONTH, YEAR

You can use built in functions like DAY, MONTH, YEAR:

SELECT YEAR(mydate) AS 'year', MONTH(mydate) AS 'month', DAY(mydate) AS 'month'
FROM mytable;

result:

MySQL where clause filter by DAY, MONTH, YEAR

Sometimes you may need to filter results byt day, month and/or year. This can be easily done by:

SELECT * FROM mytable Where DAY(mydate)='11' AND Month(mydate)='11' AND YEAR(mydate)='2017';

result:

mydate
-------------------|
2017-11-11 07:15:05
2017-11-11 08:40:09