SQL query to retrieve year from date

Question

I am trying to create a query that receives only a year from the selected dates. That is, select ASOFDATE from PSASOFDATE; returns 11/15/2012 , but I want only 2012 . How can I get only a year? I know that the YEAR function can be used, but I'm not sure how to do it.

Answer 1

 SELECT YEAR(ASOFDATE) FROM PASOFDATE 

Editted: Anyway, if your date is a string, let it convert it to the correct date format. And select a year from it.

 SELECT YEAR(STR_TO_DATE(ASOFDATE, '%d-%b-%Y')) FROM PSASOFDATE 

Since you are trying to use Toad, you can check the following code:

 SELECT EXTRACT (TO_DATE(YEAR, 'MM/DD/YY') FROM ASOFDATE) FROM PSASOFDATE; 

Link:

Answer 2

This worked for me:

 SELECT EXTRACT(YEAR FROM ASOFDATE) FROM PSASOFDATE; 

Answer 3

How about this?

 SELECT TO_CHAR(ASOFDATE, 'YYYY') FROM PSASOFDATE 

Answer 4

just pass columnName as parameter YEAR

 SELECT YEAR(ASOFDATE) from PSASOFDATE; 

another should use DATE_FORMAT

 SELECT DATE_FORMAT(ASOFDATE, '%Y') from PSASOFDATE; 

• SQLFiddle Demo

UPDATE 1

I'm sure this is a varchar value with the format MM / dd / YYYY, this is the case,

 SELECT YEAR(STR_TO_DATE('11/15/2012', '%m/%d/%Y')); 

• SQLFiddle Demo

LAST RESORT if all requests fail

use SUBSTRING

 SELECT SUBSTRING('11/15/2012', 7, 4) 

• SQLFiddle Demo

Answer 5

In SQL Server, you can use:

 SELECT DATEPART(year, ASOFDATE) FROM PSASOFDATE 

However, this does not work on Oracle.