YEAR Function in SQL Server

In this SQL Server tutorial, you will learn about the YEAR function in SQL Server to retrieve the information from the database based on the year.

First, you will understand the syntax of the YEAR() function, then where you can use the YEAR() function. Then, understand how the YEAR() function works with an example.

After that, you will learn how to use the YEAR() function with time value, the table column and the GROUP BY clause.

Table of Contents

The YEAR function in SQL Server returns the year part of the specified date; in other words, this function returns the integer value, which is the year part of the date.

The syntax used to extract the year from the data is given below.

YEAR(date);

Where,

YEAR(): This function extracts the year from the given date.
Date: This is the date from which the year part is extracted. Here, as a date, you can pass the time, datetime, datetime2, smalldatetime, or datetimeoffset value. Also, it can be the table column, a variable defined by the user or a string literal.

The return type of the YEAR() function is an integer, which means the year part returned by this function has an integer datatype.

But how exactly can you utilize this function? Suppose you need to make a yearly report; there, you can use the YEAR () function, and you can even filter the data based on year and group the data by year.

Let’s take a simple example of the YEAR() function to understand how it extracts the year part from the date.

So you have the date as ‘2024-01-02’, and to extract the year part, use the below query.

SELECT YEAR('2024-01-02') AS YearPart;

As you can see, the YEAR() function returns the year part as 2024 from the date ‘2024-01-02’. So this way, you can extract the year from any specified date. Now you understand how YEAR functions in SQL Server.

This is a simple example where you have extracted the year part from the literal date value.

YEAR Function in SQL Server with Time Value

As you know, the date also contains the time value, or if you have a timestamp, it has both date and time. What happens when only the time value is passed to the YEAR() function? That means how the YEAR() function will interpret and return the year part from the time part value of the date.

Let’s see with an example. Suppose you have a time value of ’13:23:01′ and pass it to the YEAR() function, as shown in the below query.

SELECT YEAR('13:23:01') AS YearPart;

YEAR Function in SQL Server with Time Value

You can see that the YEAR() function returns the year-part as 1900 for the time value ’13:23:01′, and if you notice, we haven’t even specified 1900 in our time value, but why YEAR() function is returning 1900 for the time value?

That is because of the way SQL Server handles dates and time values internally, so as you know, time doesn’t contain the date information. So, the SQL Server uses a default date of January 1, 1900, whenever the provided value doesn’t contain the date.

Thus, this is how to use the YEAR function in SQL Server with time value.

YEAR Function in SQL Server with Table Column

As you know, you can pass the table column to the YEAR() function, and the column must contain values of type, date, time, datetime, datetime2, smalldatetime, and datetimeoffset.

Let’s use the YEAR() function with the table column. For example, you have the CustomerSales table shown below.

YEAR Function in SQL Server with CustomerSales Table Column

Here, you have a task to find all the records or rows for the sale date in the 2021 year with the product and its total amount. For that, use the below query.

SELECT * FROM CustomerSales
WHERE YEAR(SaleDate) = 2021;

YEAR Function in SQL Server with Table Column

As you can see, the result set contains all the sales related to the 2021 year because here, we filter the sales date based on the year by specifying the YEAR(SaleDate) = 2021 in the WHERE clause.

So, in the query part, WHERE YEAR(SaleDate) = 2021, the YEAR(SaleDate) extracts the year part from all the date values in the SaleDate column and matches against the specified year 2021, then matching year records are included in the result set.

YEAR Function in SQL Server with GROUP BY Clause

You can use the YEAR() function with the GROUP BY clause to group the data by year. For example, using the CustomerSales table, you need to find the total sales by year. For that, use the below query.

SELECT YEAR(SaleDate) AS SaleYear, SUM(TotalAmount)AS YearlyTotalSales 
FROM CustomerSales
GROUP BY YEAR(SaleDate);

YEAR Function in SQL Server with GROUP BY Clause

After executing the above query, the result set contains the two columns SaleYear and YearlyTotalSales. For example, TotalSales in 2019 were 400.00$, in 2020, is 600$, and so on.

Here, the YEAR(SaleDate) function is used in the GROUP BY clause to group the date by a year, and then the SUM() function computes the sale amount for each year. so this is how to use the GROUP BY with the YEAR function in SQL Server.

Conclusion

In this SQL Server tutorial, you covered how to use the YEAR function in SQL Server and extracted the year part from the given date. Also, understand how the YEAR() function works with only the time value.

Then, you learned how to filter the records based on the year and used the YEAR() function with the GROUP BY clause to group the data based on the year.