Overview
The YEAR function returns the year as an integer from the date or datetime provided.
Explanation
Syntax
YEAR(date)Parameters
- Date – Is the date or datetime provided
Simple YEAR Example
The following example will show the year for the data March 9, 2021.
SELECT YEAR('3-9-2021 5:00:55 PM') as YEAR
YEAR function with NULL values
If the parameter of the YEAR function is NULL, a NULL value will be returned.
SELECT YEAR(NULL) as YEAR
Conversion failed error for the YEAR function
A typical error will occur if the date is invalid. The following example use a year of (10000). The maximum year value is 9999 (4 digits).
SELECT YEAR('13-02-10000 5:00:55 PM') as YEARThe error message displayed will be the following.
Conversion failed when converting date and/or time from character string.Time with the YEAR function
If the YEAR function receives just a time value, the value returned is 1900.
SELECT YEAR('5:00:55 PM') as YEAR
YEAR function using Data from a Table
The following example will show the YEAR for the EndDate column.
SELECT YEAR(EndDate) as YEAR
FROM [Production].[ProductCostHistory]
Additional Information
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
