CODEWITHSUNDEEP
mysql
Renjith R
Renjith R

Reputation: 891

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?

Upvotes: 54

Views: 127908

Answers (8)

NooNa MarJa
NooNa MarJa

Reputation: 545

SELECT EXTRACT(YEAR FROM subdateshow) FROM tbl_name;

Upvotes: 22

ram4nd
ram4nd

Reputation: 456

try this code:

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

Upvotes: 3

Boopathi
Boopathi

Reputation: 166

try this code:

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

Upvotes: 0

emcomments
emcomments

Reputation: 21

This should work if the date format is consistent:

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

Upvotes: 2

Jamshid Hashimi
Jamshid Hashimi

Reputation: 7825

You can try this:

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

Upvotes: 8

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

TRY:

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

Upvotes: 0

pgl
pgl

Reputation: 7981

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

Upvotes: 4

Gordon
Gordon

Reputation: 316939

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

Upvotes: 70

Related Questions