Formatting Dates in TSQL


 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.

Declare @Date as datetime = getdate()
       , @i as int = 200;
Declare @tb as table (Num int, Date_Format varchar(30));

WHILE @i > 0
       BEGIN TRY
             Insert into @tb
             SELECT @i, CONVERT(varchar(30), @Date, @i)
       END TRY
       BEGIN Catch
       End Catch
       Set @i -=
SELECT * FROM @tb order by 1

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:

SELECT Format(getdate(), 'M/d/yyyy')

That will give you 4/12/2013, for example.  Ah, much easier than remembering arbitrary numbers!

Mark Wojciechowicz