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

User · Answer

TRY   SELECT EXTRACT YEAR FROM  STR TO DATE subdateshow    d  m  Y

User · Answer

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

User · Answer

This should work if the date format is consistent   select SUBSTRING INDEX  subdateshow     -1   from table

User · Answer

You can try this   SELECT EXTRACT YEAR FROM field  FROM table WHERE id 1

User · Answer

try this code   SELECT DATE FORMAT FROM UNIXTIME field     Y   FROM table

User · Answer

try this code   SELECT YEAR  str to date  subdateshow    m  d  Y      AS Mydate

User · Answer

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

User · Answer

SELECT EXTRACT YEAR FROM subdateshow  FROM tbl name

[mysql] mysql extract year from date format

Examples related to mysql