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.
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;
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;
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.
SELECT YEAR(<date_string>); --or within a table-- SELECT YEAR(<column_table>) FROM <table_name>;
SELECT YEAR(ORDER_DATE) AS YEAR_OF_ORDER FROM demo_orders WHERE ITEM_NAME='Maserati';
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.
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';
day month year 20 10 2020
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.