In SQL, date formatting with versions prior to 2012 is very limited. I never can seem to remember the magic number for the right date format. Too many times I have rewritten this query below to get all the date formats, so I am posting it up for future reference.
Thankfully, this shortcoming has been gracefully addressed in SQL 2012. I was recently looking for a format that does not exist in prior versions: M/d/yyyy. With the new FORMAT() function, this can be addressed like so:
That will give you 4/12/2013, for example. Ah, much easier than remembering arbitrary numbers!