Well the point is that SQL Server is the back-end and nicely formatting things is the job of the front-end. It's just not what SQL was designed for.
It's also beneficial in other ways to pass the data to the front end as the proper type (a date) rather than a string so you can re-sort it efficiently from the client end.
If you're using VB.net, it's pretty straight forward, example below:
Dim idate As Date
idate = "2009-01-01"
MsgBox(idate.ToString("dd MMMM yyyy"))
Adding the "th", "rd" etc isn't natively supported as far as I'm aware, but there are plenty of examples for building a vb.net function to do this on the web