MySQL If you obtain the year, month and date of a known date separately

In MySQL, processing dates and times is a common task. Usually, we need to extract some information such as year, month, day, etc. from known dates. MySQL provides some built-in functions that can be easily performed. This article will explain in detail how to obtain the year, month, and day parts of known dates separately in MySQL.

1. Extraction year (Year)

To extract the year from the date, you can useYEAR()Function. This function takes a date parameter and returns the year part of the date.

Example

Suppose we have a name calledeventstable, containing a nameevent_datedate column. We can extract it through the following queryevent_dateYears in:

SELECT event_date, YEAR(event_date) AS year
FROM events;
​

Output result

2. Extract month (Month)

To extract months from dates, you can useMONTH()Function. This function takes a date parameter and returns the month part of the date (1 to 12).

Example

Similarly, fromevent_dateExtracted months:

SELECT event_date, MONTH(event_date) AS month
FROM events;

Output result

3. Extraction date (Day)

To extract the date part from the date, you can useDAY()Function. This function takes a date parameter and returns the number of days (1 to 31) of that date.

Example

fromevent_dateExtract date part:

SELECT event_date, DAY(event_date) AS day
FROM events;

Output result

IV. Comprehensive examples

We can combine the above functions to extract year, month and day information from dates at the same time.

Example

fromevent_dateExtract year, month, and date:

SELECT event_date, 
       YEAR(event_date) AS year,
       MONTH(event_date) AS month,
       DAY(event_date) AS day
FROM events;
​

Output result

V. Other date and time functions

Extract hours, minutes, seconds

In addition to year, month, and day, MySQL also provides functions to extract the time part, such asHOUR()、MINUTE()andSECOND()。

Example

Suppose there is a name calledlog_timeWe can extract hours, minutes and seconds respectively for timestamp columns:

SELECT log_time, 
       HOUR(log_time) AS hour,
       MINUTE(log_time) AS minute,
       SECOND(log_time) AS second
FROM logs;
​

Output result

Extract the weekday

Can be usedWEEKDAY()Function extracts the day of the week, from 0 (Monday) to 6 (Sunday).

Example

fromevent_dateExtract the day of the week:

SELECT event_date, WEEKDAY(event_date) AS weekday
FROM events;
​

Output result

6. Use the DATE_FORMAT function

DATE_FORMAT()Functions allow formatting dates based on specified format strings. In this way, various parts of the date can be flexibly extracted.

Example

fromevent_dateExtract year, month, and date:

SELECT event_date, 
       DATE_FORMAT(event_date, '%Y') AS year,
       DATE_FORMAT(event_date, '%m') AS month,
       DATE_FORMAT(event_date, '%d') AS day
FROM events;
​

Output result

This is the end of this article about obtaining the year, month and date of known dates in MySQL. For more related MySQL to obtain the year, month and date, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!