mySQL convert varchar to date

Question

I need to convert a varchar value of 1/9/2011 to a date in mySQL and I want only the month and year. So that I can then use the PERIOD_DIFF function (so I would need the above to be converted to 201101).

I've tried various ways using the STR_TO_DATE function:

SELECT STR_TO_DATE(CYOApp_oilChangedDate, '%m/%Y') FROM CYO_AppInfo

But I get weird results... (for example: 2009-01-00)

What am I doing incorrectly?

Answer 1

select date_format(str_to_date('31/12/2010', '%d/%m/%Y'), '%Y%m');  

or

select date_format(str_to_date('12/31/2011', '%m/%d/%Y'), '%Y%m');  

hard to tell from your example

Answer 2

As gratitude to the timely help I got from here - a minor update to above.

$query = "UPDATE `db`.`table` SET `fieldname`=  str_to_date(  fieldname, '%d/%m/%Y')";