SQL Query to Find the Year from Date

To find the year from the date, use the YEAR() function in SQL.

SQL YEAR() function returns the year component of a date as a four-digit number in the range 1900-9999.

Find Year from Data in SQL Syntax:

SELECT YEAR(<date_string>);
OR
SELECT YEAR(<column_table>) FROM <table_name>;

Here, we will discuss the steps to implement the SQL Query to Find the Year from Date.

First lets create a demo database and table.

CREATE DATABASE Beginner;
USE Beginner;
CREATE TABLE demo_orders(
 ORDER_ID INT IDENTITY(1,1) PRIMARY KEY, 
 --IDENTITY(1,1) is same as AUTO_INCREMENT in MySQL.
 --Starts from 1 and increases by 1 with each inserted row.
 ITEM_NAME VARCHAR(30) NOT NULL,
 ORDER_DATE DATE
);
INSERT INTO demo_orders 
VALUES
('Maserati', '2007-10-03'),
('BMW', '2010-07-23'),
('Mercedes Benz', '2012-11-12'),
('Ferrari', '2016-05-09'),
('Lamborghini', '2020-10-20');

Output:

Selecting Year from Date in SQL Example

Now let’s find the year of the order with ITEM_NAME as ‘Maserati’ with the help of the YEAR() function.

SELECT YEAR(ORDER_DATE) AS YEAR_OF_ORDER
FROM demo_orders
WHERE ITEM_NAME='Maserati';

We can also implement it using extract function:

SELECT EXTRACT(YEAR FROM ORDER_DATE) AS YEAR_OF_ORDER
FROM demo_orders
WHERE ITEM_NAME = 'Maserati';

This query uses the EXTRACT function to extract the year from the ORDER_DATE column. By using the EXTRACT function, you can ensure that the year is retrieved correctly even if the ORDER_DATE column contains a timestamp.

Output :

Similarly, we can find the respective day and month of the given date using DAY() and MONTH() functions.

Example:

SELECT day(order_date)[day],
       month(order_date)[month], 
       year(order_date)[year]
FROM demo_orders 
WHERE ITEM_NAME='Lamborghini';

Output :