A very nice utilitarian date formatting function that mimics VB's date format strings is available at http://www.sqlservercentral.com/scripts/contributions/1007.asp

I highly recommend it for low transaction volume systems that have odd date reformatting requirements rather than reinventing the wheel for each new format.