SQL Query to Find the Year from Date

Finding the year from a date in SQL is a common task in database management, especially when dealing with date-sensitive data such as sales records, transactions, or any kind of timestamped event. The SQL YEAR() function is a powerful tool that allows us to extract the year component of a date efficiently.

In this article, we will explain the steps to implement SQL queries for extracting the year from a date, setting up a demo database and table, using SQL functions, and applying them in practice.

1. Create a Demo Database and Table

To demonstrate how to use SQL functions for date manipulation, we need a database and a table with sample data. Let’s create a geeks database and populate it with a demo_orders table that includes ORDER_ID, ITEM_NAME, and ORDER_DATE.

Query:

-- Creating the database
CREATE DATABASE geeks;

-- Switching to the newly created database
USE geeks;

-- Creating the demo_orders table
CREATE TABLE demo_orders (
    ORDER_ID INT IDENTITY(1,1) PRIMARY KEY, 
    ITEM_NAME VARCHAR(30) NOT NULL,
    ORDER_DATE DATE
);

-- Inserting sample data into the table
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

demo_orders

2. Extracting Year Using YEAR() Function

The YEAR() function is an easy method for obtaining the year from a date. It returns the year component as a four-digit number, which ranges from 1900 to 9999. Here’s how we can find the year of a specific order: Now let’s find the year of the order with ITEM_NAME as ‘Maserati‘ with the help of the YEAR() function.

Query:

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

Output

Explanation:

This query is straightforward and directly retrieves the year from the ORDER_DATE column, regardless of whether it contains just a date or a timestamp. The YEAR() function takes a date as input and outputs the year in the correct format, making it ideal for filtering, grouping, and analyzing data by year.

3. Using the EXTRACT Function for Flexibility

Sometimes we might need more control over what specific part of a date or timestamp we wish to extract. The EXTRACT function is ideal for this purpose. It allows us to specify which part (e.g., year, month, day) to extract from a date or timestamp.

Query:

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

Output

Explanation:

The EXTRACT function is useful when we need to be explicit about the unit we’re extracting. In this case, EXTRACT(YEAR FROM ORDER_DATE) is used to clearly specify that we want to extract the year component from the ORDER_DATE column. This is particularly useful when our date field might include time (timestamp), ensuring that only the date part is considered for the extraction.

4. Finding Day and Month from Date

To get the day and month from a given date, we can use the DAY() and MONTH() functions, respectively. This query shows how to combine functions to extract multiple parts of a date, making it easier to filter and analyze your data.

Query:

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

Output

Explanation:

By combining the DAY(), MONTH(), and YEAR() functions, this query extracts the specific day, month, and year from the ORDER_DATE field for orders of ITEM_NAME ‘Lamborghini‘. This approach is useful when we need to break down date information for reporting, analysis, or sorting within our database.

Conclusion

The YEAR() function in SQL is an essential tool for anyone working with dates in databases. Whether we’re analyzing sales data, managing inventory, or simply organizing records, knowing how to extract the year from a date can greatly enhance our ability to sort, filter, and report on our data effectively. By using the YEAR() and EXTRACT functions, we can ensure accurate and relevant data extraction, making the way for efficient database management and analysis.

remmargorpp

Follow

Improve

Article Tags :

• Databases
• SQL
• dbms
• DBMS-SQL