MySQL - YEAR() Function



The MySQL YEAR() function is used to retrieve and return the year of the given date or, date time expression.

This function accepts the date value in the 'YYYY-MM-DD' format as an argument and retrieves the 'YYYY' from it. The year value is retrieved as a result-set. Hence, this function returns a numerical value ranging from 0000 to 9999.

A NULL value is returned if the date argument passed to the function is NULL.

Syntax

Following is the syntax of MySQL YEAR() function −

YEAR(date);

Parameters

This method accepts the date value from which you need to retrieve the year as a parameter.

Return value

This function returns the year of the given date or, date time expression.

Example

In the following query, we are using the MySQL YEAR() function to fetch the year of the given date expression −

SELECT YEAR('2023-11-20') As Result;

Output

This will produce the following result −

Result
2023

Example

If the YEAR value in the given date is 0 this function returns 0 as result −

SELECT YEAR('0000-08-20') As Result;

Following is the output −

Result
0

If we pass an empty string or a non-string value as an argument this function returns NULL.

SELECT YEAR('') As Result;

Following is the output −

Result
NULL

Here, we are passing a non-string value as an argument. Thus, it returns NULL as result −

SELECT YEAR(1990-11-11) As Result;

Following is the output −

Result
NULL

Example

Here, we are retrieving the year segment of the current date (2023-11-20) −

SELECT YEAR(CURDATE()) As Result;

Output

Following is the output −

Result
0

Example

In the following example, let us create a table named ORDERS using CREATE TABLE statement −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL YEAR() function to fetch the year segment of each date value in the "DATE" column.

Select OID, DATE, YEAR(DATE) As Result From ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 2009
100 2009-10-08 00:00:00 2009
101 2009-11-20 00:00:00 2009
103 2008-05-20 00:00:00 2008
Advertisements