How to Extract Year from Date in SQL
Summary: in this tutorial, you will learn how to extract the year from a date in SQL by using various functions.
Given a DATE or DATETIME value, you can extract the year from it by using the EXTRACT() function. The following illustrates the syntax:
The syntax is straightforward. The date can be a date literal or an expression that evaluates to a date value.
The EXTRACT() function returns a number which represents the year of the date.
The following example shows how to extract the year from the date of July 22nd 2018 :
The result is 2018 as we expected:
To get the current year, you pass the current date to the EXTRACT() function as follows:
The EXTRACT() function is a SQL standard function supported by MySQL, Oracle, PostgreSQL, and Firebird.
If you use SQL Server, you can use the YEAR() or DATEPART() function to extract the year from a date.
For example, the following statement returns the current year in SQL Server:
Similar to SQL Server, MySQL also supports the YEAR() function to return the year from a date.
In SQLite, you use the strftime() function to get the current year from a date as shown in the following query:
In this tutorial, you have learned how to use various functions to extract the year from a date.
Getting only Month and Year from SQL DATE
I need to access only Month.Year from Date field in SQL Server.
30 Answers 30
As well as the suggestions given already, there is one other possiblity I can infer from your question:
— You still want the result to be a date
— But you want to ‘discard’ the Days, Hours, etc
— Leaving a year/month only date field
This gets the number of whole months from a base date (0) and then adds them to that base date. Thus rounding Down to the month in which the date is in.
SQL Server YEAR Function
Summary: in this tutorial, you will learn how to use the SQL Server YEAR() function to extract a year from a date.
SQL Server YEAR() function overview
The YEAR() function returns an integer value which represents the year of the specified date.
The following shows the syntax of the YEAR() function:
The function accepts an argument which can be a literal date value or an expression that can resolve to a TIME , DATE , SMALLDATETIME , DATETIME , DATETIME2 , or DATETIMEOFFSET value.
The YEAR() function returns the same value as the following DATEPART() function:
SQL Server YEAR() function examples
A) Using YEAR() function with a literal date value
This example uses the YEAR() function to extract a year from the date ‘2019-02-01’:
B) Using YEAR() function with a date value that has only time part
If the input date value has only time data, the YEAR() function will return 1900 :
Here is the output:
C) Using YEAR() function with table columns example
We will use the sales.orders and sales.order_items from the sample database for demonstration.
This example uses the YEAR() function to extract year data from the values in the shipped_date column. It returns the gross sales by year using the SUM() function and GROUP BY clause:
The following shows the output:
In this tutorial, you have learned how to extract the year from a specified date by using the SQL Server YEAR() function.
SQL Server функция YEAR
В SQL Server (Transact-SQL) функция YEAR возвращает четырехзначный год (как число) с учетом значения даты.
Синтаксис
Синтаксис функции YEAR в SQL Server (Transact-SQL):
Параметры или аргументы
date_value — дата из которой можно извлечь четырехзначное значение года.
Примечание
- См. Также функции DATEPART и DATENAME.
Применение
Функция YEAR может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Пример
Рассмотрим некоторые примеры SQL Server функции YEAR, чтобы понять, как использовать функцию YEAR в SQL Server (Transact-SQL). Например: