It depends on what datatype your column is.
If it's VARCHAR or NVARCHAR, then it's a problem. If it's DATETIME, then it's pretty simple - but probably not advisable.
If you have it stored as a string based column, then you need to change that and convert the data to DATETIME before somehow a "bad" value gets in there.
Even as a DATETIME value, it's not a good idea to do this in SQL: it should be done in your presentation software, not the DB. Why? Because for an English user you need JAN, FEB, ... but for a French user it should be JAN, FEV, ... and SQL server doesn't have access to your user culture, unlike your presentation software.
But...in SQL it's pretty easy:
SELECT SUBSTRING(CONVERT(VARCHAR(11), MyDateColumn, 6), 4, 6)