mysql extract year from date format

Question

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?

Answer 1

Since your subdateshow is a VARCHAR column instead of the proper DATE, TIMESTAMP or DATETIME column you have to convert the string to date before you can use YEAR on it:

SELECT YEAR(STR_TO_DATE(subdateshow, "%m/%d/%Y")) from table 

See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Answer 2

SELECT EXTRACT(YEAR FROM subdateshow) FROM tbl_name;