How to Get the Year from a Datetime Column in MySQL

Problem:

You’d like to get the year from a date/datetime column in a MySQL database.

Example:

Our database has a table named conference with data in the columns id, name, and start_datetime.

For each conference, let’s get its name and year. We’ll need to get only the year from the start_datetime column.

Solution:

We’ll use the YEAR() function. Here’s the query you’d write:

SELECT 
  name,
  YEAR(start_datetime) AS year_of_conference
FROM conference;

Here’s the result of the query:

Discussion:

Use the YEAR() function to retrieve the year value from a date/datetime/timestamp column in MySQL. This function takes only one argument – a date or date and time. This can be the name of a date/datetime/timestamp column or an expression returning one of those data types. (In our example, it is the column start_datetime of the date data type.)

YEAR() returns the year as an integer from 1000 to 9999. The 'Social Media World' conference's start date is 2019-02-20 14:15:34, so YEAR() returned 2019 for this record.