Today I was tasked with formating a SQL Datetime field as part of a FOR XML query.  More often then not, I can get away with formating in the presentation layer and leave all dates in the standard ISO8500.  Today I needed a nice looking date to be concatenated within a business name.  I stumbled across this approach which uses the Convert method within SQL 2005.

• MON DD YYYY HH:MIAM (OR PM) — Feb 5 2003 5:54AM
  CONVERT(CHAR(19),GETDATE())
• MM-DD-YY FORMAT — 02-05-03
  CONVERT(CHAR(8),GETDATE(),10)
• MM-DD-YYYY FORMAT — 02-05-2003
  CONVERT(CHAR(10),GETDATE(),110)
• DD MON YYYY FORMAT — 05 Feb 2003
  CONVERT(CHAR(11),GETDATE(),106)
• DD MON YY FORMAT — 05 Feb 03
  CONVERT(CHAR(9),GETDATE(),6)
• DD MON YYYY HH:MM:SS:MMM(24H) — 05 Feb 2003 05:54:39:567
  CONVERT(CHAR(24),GETDATE(),113)