SQL Server YEAR Function - Features, Examples and Equivalents

YEAR function returns the year of the specified datetime value.

Syntax YEAR(datetime)
Quick Example SELECT YEAR('2010-09-17');
Return Type INT
Alternative DATEPART(YEAR, datetime)
Note Returns 1900 if datetime contains only time part

Version: SQL Server 2008 R2

Related Functionality in SQL Server

Related functions for YEAR in SQL Server:

DATEPART(part, datetime) Returns the specified part of datetime value
MONTH(datetime) Returns the month
DAY(datetime) Returns the day
DATEDIFF Returns datetime difference in seconds, days, months, weeks etc.

SQL Server YEAR Function Details

YEAR function returns the year from a datetime expression or string:

  SELECT YEAR('2010-09-17');
  -- Result: 2010
 
  SELECT YEAR(GETDATE());
  -- Result: 2011
 
  -- Time only
  SELECT YEAR('11:11');
  -- Result: 1900

SQL Server YEAR in Other Databases

Extracting the year in other databases:

Oracle EXTRACT(YEAR FROM datetime)
MySQL YEAR(datetime) Returns NULL if datetime contains time only
PostgreSQL EXTRACT(YEAR FROM datetime)

SQL Server YEAR Function Conversion to Other Databases

Extracting the year:

Oracle:

In Oracle you can use EXTRACT function to get the year from a datetime value. Note that string literals must be explicitly cast to DATE or TIMESTAMP data types:

   SELECT EXTRACT(YEAR FROM DATE '2010-09-17') FROM dual;
   -- Result: 2010
 
   SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) FROM dual;
   -- Result: 2011

MySQL:

MySQL supports YEAR function, so no conversion is required. Note that MySQL YEAR returns NULL if datetime value contains the time part only:

   SELECT YEAR('2010-09-17');
   -- Result: 2010
 
   SELECT YEAR(CURRENT_TIMESTAMP);
   -- Result: 2011
 
   SELECT YEAR('11:11');
   -- Result: NULL

PostgreSQL:

PostgreSQL EXTRACT function can be used to extract the year from a datetime value. Note that string literals must be explicitly cast to DATE or TIMESTAMP data types:

   SELECT EXTRACT(YEAR FROM DATE '2010-09-17');
   -- Result: 2011
 
   SELECT EXTRACT(YEAR FROM NOW());
   -- Result: 2011

Convert Online

Resources