September 17, 2021

The SQL YEAR function evaluates the input date and returns the year part as an integer.Description

The YEAR function works the same as the DATEPART (year, date) and returns the year part of the specified date.

SQL YEAR Usage Notes

The only argument for the YEAR function is the date. It can be a database column value, string expression, or a user-defined variable. The data type can be time, date, datetime, smalldatetime, datetime2, or datetimeoffset.

If the date value does not contain a year part, then the value of 1900, the base year value, returns.

The return data type is an integer value.

Syntax

YEAR (date)

SQL YEAR Function Examples

The following examples show the use of the SQL YEAR function.

SELECT YEAR('2021/08/16 07:35') AS ExampleYear;
/* Answer */
SELECT YEAR('2021/08/16 07:35') AS ExampleYear;

The following query returns years from which products in the AdentureWorks2019 database were available for selling.

SELECT YEAR(SellStartDate) SellYear FROM Production.Product
/* Answer */
SELECT YEAR(SellStartDate) SellYear 
FROM Production.Product

The following query displays the total number of products on sale each year. You’ll see the SQL YEAR function within the GROUP BY clause!

SELECT YEAR(StartDate) AS year, COUNT(ProductID) AS product_id FROM Sales.SpecialOffer s INNER JOIN Sales.SpecialOfferProduct p ON s.SpecialOfferID = p.SpecialOfferID WHERE StartDate IS NOT NULL GROUP BY YEAR(StartDate) ORDER BY year;
/* Answer */
SELECT YEAR(StartDate) AS year, COUNT(ProductID) AS product_id
FROM Sales.SpecialOffer s 
    INNER JOIN Sales.SpecialOfferProduct p ON s.SpecialOfferID = p.SpecialOfferID
WHERE StartDate IS NOT NULL
GROUP BY YEAR(StartDate)
ORDER BY year;

The inner join combines Special Offer and Special Offer Product tables by Special Offer id. The where clause checks for the NULL sale start date. Group by clause combines all the sale products in a year. Order by clause displays the result rows in ascending order of the year.

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>