SQL YEAR Function (Transact SQL)

You can use the SQL Year function to return just the year portion of a date. The value returned is an integer.

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

In this example you can see how YEAR returns 2021 from the full date.

/* 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.

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

Here we use YEAR to assist in grouping the data into annual buckets.

/* 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

• DATEPART Function
• Learn to use SQL Date Functions