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 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. |
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
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) |
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