[mysql] mysql extract year from date format

I need a mysql query to extract the year from the following date format from a table in my database.

For eg :

        subdateshow
      ----------------      
        01/17/2009
        01/17/2009
        01/17/2009
        01/17/2009
        01/17/2009

the following query didn't working

select YEAR ( subdateshow ) from table 

The column type is varchar. Is there any way to solve this?

try this code:

SELECT DATE_FORMAT(FROM_UNIXTIME(field), '%Y') FROM table

This should work:

SELECT YEAR(STR_TO_DATE(subdateshow, '%m/%d/%Y')) FROM table;

eg:

SELECT YEAR(STR_TO_DATE('01/17/2009', '%m/%d/%Y'));  /* shows "2009" */

You can try this:

SELECT EXTRACT(YEAR FROM field) FROM table WHERE id=1

TRY:

SELECT EXTRACT(YEAR FROM (STR_TO_DATE(subdateshow, '%d/%m/%Y')));

This should work if the date format is consistent:

select SUBSTRING_INDEX( subdateshow,"/",-1 ) from table 

SELECT EXTRACT(YEAR FROM subdateshow) FROM tbl_name;

try this code:

SELECT YEAR( str_to_date( subdateshow, '%m/%d/%Y' ) ) AS Mydate