SELECT FROM_UNIXTIME(mycolumn)
FROM mytable
select from_unixtime(column,'%Y-%m-%d') from myTable;
The function STR_TO_DATE(COLUMN, '%input_format') can do it, you only have to specify the input format. Example : to convert p052011
SELECT STR_TO_DATE('p052011','p%m%Y') FROM your_table;
The result : 2011-05-00
Source: Stackoverflow.com