One of the most frequently asked questions in SQL Server forums is how to format a
datetime value or column into a specific date format. Here's a summary of
the different date formats that come standard in SQL Server as part of the CONVERT
function. Following the standard date formats are some extended date
formats that are often asked by SQL Server developers.

It is worth to note that the output of these date formats are of VARCHAR data
types already and not of DATETIME data type. With this in mind,
any date comparisons performed after the datetime value has been formatted are
using the VARCHAR value of the date and time and not its original DATETIME
value.

The SQL statements used below to return the different date formats use the SYSDATETIME() date function, which is new to SQL Server 2008. The
SYSDATETIME() function returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The SYSDATETIME()
function used below can be replaced by the GETDATE() or GETUTCDATE() functions. The results will be the same unless the date format includes the
nanosecond portion of the time.

To make the date format results consistent, the date and time used to generate the sample output is June 8, 2011 1:30:45.9428675 PM.