SQL Server YEAR() Function

The YEAR() function is one of the date and time functions in SQL Server, used to extract the year from a given date/time value. The function takes a date/time expression as its argument and returns the year represented by that date/time expression.

Syntax

YEAR(date)

Arguments:

• date: Required. The date/time expression from which to extract the year. It can be an expression of the data types datetime, date, time, smalldatetime, datetime2, or datetimeoffset.

Return Value: An integer value representing the year represented by the given date/time expression.

Usage

The YEAR() function can be used to extract the year from a date/time column and to calculate annual data in aggregate functions.

Common use cases include:

• Extracting the year from a date/time column for grouping and aggregating data by year in reports or analysis.
• Calculating the total sales or other metrics for a year.
• Analyzing historical data by year.

Examples

Here are two examples of the YEAR() function:

Example 1

Suppose we have a table named orders that contains order information and order dates. We want to calculate the number of orders for each year.

SELECT YEAR(order_date) AS order_year, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date)
ORDER BY YEAR(order_date)

The result will return the number of orders for each year.

Example 2

Suppose we have a table named employees that contains employee information and hire dates. We want to calculate the number of employees hired each year.

SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS employee_count
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY YEAR(hire_date)

The result will return the number of employees hired each year.

Conclusion

The YEAR() function is a useful date/time function that can extract the year from a date/time column and be used in aggregate functions to calculate annual data. By using the YEAR() function, it is easy to extract the year from date/time data and use it for grouping and aggregation.