YEAR() Function in MySQL

YEAR() function in MySQL is used to find year from the given date. If the date is NULL, the YEAR() function will return NULL. Otherwise, it returns value range from 1000 to 9999.

Syntax :

YEAR(date)  

Parameter : This method accepts one parameter as mentioned above and described below :

• date : The date or datetime from which we want to extract the year.

Returns : It returns the value range from 1000 to 9999.

Example-1 : Finding the Current Year Using Year() Function.

SELECT YEAR(NOW()) AS Current_Year;

Output :

mysql> SELECT YEAR(NOW()) AS Current_Year;
+--------------+
| Current_Year |
+--------------+
|         2020 |
+--------------+
1 row in set (0.00 sec)

Example-2 : Finding the Year from given datetime Using Year() Function.

SELECT YEAR('2015-09-26 08:09:22') AS Year ;

Output :

+------+
| Year |
+------+
| 2015 |
+------+

Example-3 : Finding the Year from given datetime Using Year() Function when the date is NULL.

SELECT YEAR(NULL) AS Year ;

Output :

+------+
| Year |
+------+
| NULL |
+------+

Example-4 : The YEAR function can also be used to find total product sold in a year. To demonstrate create a table named.

Product :

CREATE TABLE Product(
    Product_id INT AUTO_INCREMENT, 
    Product_name VARCHAR(100) NOT NULL,
    Buying_price DECIMAL(13, 2) NOT NULL,
    Selling_price DECIMAL(13, 2) NOT NULL,
    Selling_Date Date NOT NULL,
    PRIMARY KEY(Product_id)
);

Now inserting some data to the Product table :

INSERT INTO 
    Product(Product_name, Buying_price, Selling_price, Selling_Date)
VALUES
    ('Audi Q8', 10000000.00, 15000000.00, '2018-01-26' ),
    ('Volvo XC40', 2000000.00, 3000000.00, '2018-04-20' ),
    ('Audi A6', 4000000.00, 5000000.00, '2018-07-25' ),
    ('BMW X5', 5000500.00, 7006500.00, '2018-10-18'  ),
    ('Jaguar XF', 5000000, 7507000.00, '2019-01-27'  ),
        ('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2019-09-01'  ),
        ('Jaguar F-PACE', 5000000.00, 7000000.00, '2019-12-26'  ),
    ('Porsche Macan', 6500000.00, 8000000.00, '2020-06-16' ) ;

So, Our table looks like :

mysql> SELECT * FROM Product;
+------------+-----------------------+--------------+---------------+--------------+
| Product_id | Product_name          | Buying_price | Selling_price | Selling_Date |
+------------+-----------------------+--------------+---------------+--------------+
|          1 | Audi Q8               |  10000000.00 |   15000000.00 | 2018-01-26   |
|          2 | Volvo XC40            |   2000000.00 |    3000000.00 | 2018-04-20   |
|          3 | Audi A6               |   4000000.00 |    5000000.00 | 2018-07-25   |
|          4 | BMW X5                |   5000500.00 |    7006500.00 | 2018-10-18   |
|          5 | Jaguar XF             |   5000000.00 |    7507000.00 | 2019-01-27   |
|          6 | Mercedes-Benz C-Class |   4000000.00 |    6000000.00 | 2019-09-01   |
|          7 | Jaguar F-PACE         |   5000000.00 |    7000000.00 | 2019-12-26   |
|          8 | Porsche Macan         |   6500000.00 |    8000000.00 | 2020-06-16   |
+------------+-----------------------+--------------+---------------+--------------+

Now, we are going to find number of product sold per year by using YEAR() function.

SELECT 
    YEAR(Selling_Date) year, 
    COUNT(Product_id) Product_Sold
FROM 
    Product
GROUP BY YEAR(Selling_Date)
ORDER BY YEAR(Selling_Date);

Output :

+------+--------------+
| year | Product_Sold |
+------+--------------+
| 2018 |            4 |
| 2019 |            3 |
| 2020 |            1 |
+------+--------------+

jana_sayantan

Follow

Improve

Article Tags :

• SQL
• DBMS-SQL
• mysql