Back to cookbooks list Articles Cookbook

How to Get the Year from a Datetime Column in MySQL

  • YEAR()

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.

idnamestart_datetime
1Social Media World2019-02-20 14:15:34
2Mobile World 20172017-08-31 20:10:14
3Electronics Show2018-04-03 10:05:45
4Tech Asia 20192019-01-01 12:47:54

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:

nameyear_of_conference
Social Media World2019
Mobile World 20172017
Electronics Show2018
Tech Asia 20192019

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.

Recommended courses:

Recommended articles:

See also: