Open In App

SQL Query to Find the Year from Date

Improve
Improve
Like Article
Like
Save
Share
Report

Overview :
You will be able to understand the implementation to Find the Year from Date in SQL with the help of an example. Here we will see, how to find or extract the year from the given date in MS SQL Server’s database table with the help of SQL query. For the purpose of demonstration, we will be creating a demo_orders table in a database called “geeks“.

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

Step-1: Creating the Database :
Use the below SQL statement to create a database called geeks as follows.

CREATE DATABASE geeks;

Step-2: Using the Database :
Use the below SQL statement to switch the database context to geeks as follows.

USE geeks;

Step-3 : Creating Table :
We have the following demo table in our geeks database.

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
);

Step-4: Verifying table :
You can use the below statement to query the description of the created table as follows.

EXEC SP_COLUMNS demo_orders;

Output :

TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH REMARKS
demo_orders ORDER_ID 4 int identity 10 4 NULL
demo_orders ITEM_NAME 12 varchar 30 30 NULL
demo_orders ORDER_DATE -9 date 10 20 NULL

Step-5: Adding data to the table :
Use the below statement to add data to the demo_orders table as follows.

INSERT INTO demo_orders 
--no need to mention columns explicitly as we are inserting into all columns and ID gets 
--automatically incremented.
VALUES
('Maserati', '2007-10-03'),
('BMW', '2010-07-23'),
('Mercedes Benz', '2012-11-12'),
('Ferrari', '2016-05-09'),
('Lamborghini', '2020-10-20');

Step-6: Verifying Inserted data :
To verify the contents of the table use the below statements as follows.

SELECT * FROM demo_orders;

Output :

ORDER_ID ITEM_NAME ORDER_DATE
1 Maserati 2007-10-03T00:00:00.000Z
2 BMW 2010-07-23T00:00:00.000Z
3 Mercedes Benz 2012-11-12T00:00:00.000Z
4 Ferrari 2016-05-09T00:00:00.000Z
5 Lamborghini 2020-10-20T00:00:00.000Z

Step-7: Implementation of Query to Find the Year from Date :
Now let’s find the year of the order with ITEM_NAME as ‘Maserati’ with the help of the YEAR() function. Below is the syntax to retrieve the year from the given date.

Syntax –

SELECT YEAR(<date_string>);
--or within a table--
SELECT YEAR(<column_table>) FROM <table_name>;

Example –

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 :

YEAR_OF_ORDER
2007

Step-8: Implementation of Query to Find day, month and year from Date :
Similarly, we can find the respective day and month of the given date using DAY() and MONTH() functions.

Example –

SELECT day(order_date)[day], --here [day] is the identifier name for the column in the output.
       month(order_date)[month], 
       year(order_date)[year]
FROM demo_orders 
WHERE ITEM_NAME='Lamborghini';

Output :

day month year
20 10 2020

Last Updated : 26 Jun, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads