Mysql release year from date format

Question

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

For example,

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

following request did not work

 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 correct DATE, TIMESTAMP or DATETIME column, you need to convert the string to a 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; 

Answer 3

You can try the following:

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

Answer 4

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" */ 

Answer 5

try this code:

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

Answer 6

This should work if the date format is consistent:

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

Answer 7

TRY:

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

Answer 8

try this code:

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