How to Extract the Year from a Datetime Column in MySQL

If you have been dealing with numerous databases, I am pretty sure that you may encounter tables where the datetime column contains both date and time. But what if you only want to extract the year? How are you going to retrieve that specific part of the data from the database? In this tutorial, we will explore how to extract the year from a datetime column in MySQL using practical examples. Let’s get started!

Understanding Datetime

MySQL datetime data type is very useful when we are working with data that has to be tracked over a certain period of time. The datetime column stores the data such that the date and the time are in the format of “YYYY-MM-DD HH:MM:SS”. This format can be useful for logging events, transactions or any temporal data.

However, there are scenarios where the entire datetime is not necessary. For example, if you want to generate reports, and analyze trends, it’s crucial to extract some specific part of this value specifically the year.

DATE, TIME and DATETIME have distinct uses where DATE contains date information (YYYY-MM-DD), TIME contains time information (HH:MI:SS) and DATETIME combines both these.

Also Read: MySQL ADDDATE() – Adding Numbers to DateTime Values in MySQL

Extracting the Year from a Datetime Column

Let’s say you are the cashier at the grocery store, where you are maintaining a customer record that contains datetime column, and you need to extract the year from that column to make an annual report. So, how are you going to do that? Don’t worry, I will show you how.

MySQL provides several functions to manipulate and extract parts of datetime values. Among them, the best ones are the YEAR() and DATE FORMAT() functions. Let’s see how to use them.

MySQL YEAR() Function

The YEAR() function in MySQL takes a date or datetime value and returns the year as a four-digit number. This can turn out very useful while generating reports, and analyzing trends or transactions.

Syntax:

SELECT YEAR(datetime_column) FROM table_name;Code language: SQL (Structured Query Language) (sql)

Where,

YEAR(datetime_column): Extracts the year from the datetime_column

This query will extract years from each row, but there are scenarios where we want an overview of each unique year present in the dataset, to do so:

SELECT YEAR(datetime_column), COUNT(*) 
FROM table_name 
GROUP BY YEAR(datetime_column);Code language: SQL (Structured Query Language) (sql)

Example 1: Extracting Year Using YEAR() Function

Suppose we have a table named Customer_orders that records the order details, including the order date of the grocery store.

Now, let’s say that you want to extract the year from the “order_date” column such that you can maintain the records yearly. To do so, we can use the “YEAR()” function as follows:

SELECT order_id, customer_id, YEAR(order_date) AS order_year, total_amount
FROM customer_orders;Code language: SQL (Structured Query Language) (sql)

Output:

Year Is Extracted From customer_orders Table — Extracting Year Using YEAR()

As we can see from the above example, the “YEAR()” function retrieves the year from the “order_date” column for each record.

Example 2: Extracting Year Using YEAR() Function

Let’s say we want an overview of each unique year present in the customer_orders table to know more about customer behaviour.

SELECT YEAR(order_date), COUNT(*) 
FROM customer_orders 
GROUP BY YEAR(order_date);Code language: SQL (Structured Query Language) (sql)

Output:

As we can see from above the data, the number of orders varies each year, which gives us insights into customer behaviour and business trends over time.

MySQL DATE_FORMAT() Function

When it comes to working with dates in MySQL, the DATE_FORMAT() Function allows us to format any date or datetime data to our specifications.

Here is how the query might look if we were only interested in the year portion:

SELECT DATE_FORMAT(your_datetime_column, '%Y')
FROM table_name;Code language: SQL (Structured Query Language) (sql)

Where,

DATE_FORMAT(order_date, ‘%Y’): Formats the order_date column to extract only the year as a four-digit number.
order_date: The column which contains date and time.
‘%Y’: Format specifier to extract the year.

Although YEAR() is a more direct method for only extracting the year, this function offers you additional customization options for how the date output is formatted.

Example 1: Extracting Year Using DATE_FORMAT() Function

If you want to extract the year using the “DATE_FORMAT()” in the customer_orders table, this is how the query will look:

SELECT order_id, customer_id, DATE_FORMAT(order_date, '%Y') AS order_year, total_amount
FROM customer_orders;Code language: SQL (Structured Query Language) (sql)

Output:

Extracting Year Using DATE TIME — Extracting Year Using DATE_FORMAT()

Common Mistakes to Avoid

NULL Values: If your column has no date data or if the date data is NULL, YEAR() returns NULL. Always double-check your data, before using a function like YEAR().
Incorrect Data Types: YEAR() won’t function properly if your dates are saved as text instead of actual datetime data types. Convert them into an appropriate datetime format before extracting parts of text-based dates.

Conclusion

When we are dealing with databases, extracting the year from a datetime column in MySQL is a common task, and we can do it easily using the “YEAR()” function. If you are filtering records or summarizing sales, it is important to know how to work with datetime value. After reading this tutorial, we hope you can easily extract and use the year part of your datetime columns in MySQL.

Reference

https://stackoverflow.com/questions/70196080/extracting-data-from-only-the-year