SQL YEAR() Function


The SQL YEAR() is a function, and returns or extract year portion(a four digit number) of a given input date value.

The SQL YEAR() function is supports only date or datetime based table columns or fields.

It can be used in SELECT statement as well in where clause.


Search Keys

  • sql year function
  • sql year
  • sql server year function
  • sql date year
  • sql this year
  • sql get year
  • select year from date sql
  • sql server get year from date
  • sql extract year
  • sql year date
  • sql week of year
  • extract year sql
  • year function sql
  • sql day of year
  • sql year month

SQL YEAR() Syntax

The below syntax is used to get year value from a given input date value using YEAR() function.

For SQL SERVER / MY SQL / MS ACCESS


SELECT YEAR('valid_date_value');

The below syntax is used to read year portion value from a given table column using YEAR() function

For MS ACCESS / SQL SERVER / MY SQL


SELECT YEAR(date_column_name1) FROM table_name;


SQL YEAR() Example - Using Expression Or Formula

The following SQL SELECT statement will extract the year value from a given input date value. We use now() function to get the input date value.


SELECT 
NOW() AS 'Current Date and Time', 
YEAR(NOW()) AS 'Year Value';

The result of above query is:

Current Date and Time Year Value
24-08-2012 19:37:30 2012

Sample Database Table - BookOrder

BookID BookName OrderDate Qty DeliveryDate
1 Foundations Of Sql Server 2008 25-08-1999 14:03:22 13 28-09-1999 09:01:39
2 My SQL Complete Reference 19-09-2007 20:26:04 3 23-09-2007 14:05:09
3 Simply Oracle 22-03-2009 16:02:06 8 27-03-2009 17:37:52
4 Postgresql Official Documentation 14-06-2005 18:37:10 1 17-06-2005 15:53:26
5 SQL Server Query Performance Tuning 21-04-2011 22:14:36 5 25-04-2011 23:33:32
6 Art Of SQL 23-07-1996 19:12:22 3 26-07-1996 11:58:03
7 MySql Query Performance Tuning 12-01-1995 04:39:50 2 25-01-1995 10:17:33
8 Securing Oracle 13-10-2008 02:37:14 7 22-11-2008 17:05:26
9 Oracle Fundamentals 12-02-2003 06:33:37 7 19-03-2003 21:42:42
10 The Complete Guide to SQL 18-04-2001 21:49:20 7 22-04-2001 09:48:14
11 Teach Yourself SQL 15-03-2002 14:24:54 12 17-03-2002 17:55:29
12 Oracle Database 11G New Features 15-10-1998 22:32:21 2 21-10-1998 06:07:51

SQL YEAR() Example - With Table Column

The following SQL SELECT statement display the column "OrderDate" and "DeliveryDate" from the "BookOrder" table. The YEAR() function will extract the year value(a four digit number) from the column "OrderDate" and we will stored that value to a new column called "Order Year" and "Delivery Year".


SELECT 
OrderDate, YEAR(OrderDate) As 'Order Year', 
DeliveryDate, YEAR(DeliveryDate) As 'Delivery Year' 
FROM BookOrder;

The result of above query is:

OrderDate Order Year DeliveryDate Delivery Year
25-08-1999 14:03:22 1999 28-09-1999 09:01:39 1999
19-09-2007 20:26:04 2007 23-09-2007 14:05:09 2007
22-03-2009 16:02:06 2009 27-03-2009 17:37:52 2009
14-06-2005 18:37:10 2005 17-06-2005 15:53:26 2005
21-04-2011 22:14:36 2011 25-04-2011 23:33:32 2011
23-07-1996 19:12:22 1996 26-07-1996 11:58:03 1996
12-01-1995 04:39:50 1995 25-01-1995 10:17:33 1995
13-10-2008 02:37:14 2008 22-11-2008 17:05:26 2008
12-02-2003 06:33:37 2003 19-03-2003 21:42:42 2003
18-04-2001 21:49:20 2001 22-04-2001 09:48:14 2001
15-03-2002 14:24:54 2002 17-03-2002 17:55:29 2002
15-10-1998 22:32:21 1998 21-10-1998 06:07:51 1998

Search Keys

  • sql date get year
  • t sql year function
  • sql date functions
  • sql system date
  • sql now date
  • sql datetime functions
  • sql date convert
  • sql server date now
  • date time sql
  • date format sql
  • sql date compare
  • sql date comparison
  • sql datepart function
  • sql date example
  • sql datetime to date