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