Skip to content
Related Articles

Related Articles

Improve Article
SQL Query to Find the Year from Date
  • Difficulty Level : Basic
  • Last Updated : 26 Apr, 2021

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_NAMECOLUMN_NAMEDATA_TYPETYPE_NAMEPRECISIONLENGTHREMARKS
demo_ordersORDER_ID4int identity104NULL
demo_ordersITEM_NAME12varchar3030NULL
demo_ordersORDER_DATE-9date1020NULL

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_IDITEM_NAMEORDER_DATE
1Maserati2007-10-03T00:00:00.000Z
2BMW2010-07-23T00:00:00.000Z
3Mercedes Benz2012-11-12T00:00:00.000Z
4Ferrari2016-05-09T00:00:00.000Z
5Lamborghini2020-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';

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 :

daymonthyear
20102020

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :