21/08/2011  |   

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.

Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)		 Default SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100) Jun 8 2011 1:30PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY] 06/08/11
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY] 06/08/2011
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD] 11.06.08
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD] 2011.06.08
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY] 08/06/11
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY] 08/06/2011
DD.MM.YY German SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY] 08.06.11
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] 08.06.2011
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY] 08-06-11
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY] 08-06-2011
DD Mon YY 1 – SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY] 08 Jun 11 1
DD Mon YYYY 1 – SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY] 08 Jun 2011 1
Mon DD, YY 1 – SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY] Jun 08, 11 1
Mon DD, YYYY 1 – SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY] Jun 08, 2011 1
HH:MM:SS – SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)		 13:30:45
Mon  D YYYY  H:MI:SS.NNNNNNNAM (or PM) 1 Default +
nanoseconds		 SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)		 Jun  8 2011  1:30:45.9428675PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY] 06-08-11
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY] 06-08-2011
YY/MM/DD – SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD] 11/06/08
YYYY/MM/DD – SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD] 2011/06/08
YYMMDD ISO SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD] 110608
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD] 20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1 Europe default + nanoseconds SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13)
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)		 08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H) – SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)]
SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]		 13:30:45.9428675
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120) 2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h) ODBC Canonical
(with nanoseconds)		 SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121) 2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AM – SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22) 06/08/11  1:30:45 PM
YYYY-MM-DD – SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23) 2011-06-091
HH:MI:SS (24h) – SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24) 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN – SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25) 2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNN ISO8601 SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126) 2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130) 08 Jun 2011  1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAM Kuwaiti SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131) 08/06/2011  1:30:45.9428675PM

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), ‘/’, ‘-‘) AS [YY-MM-DD]
 11-06-08
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 111), ‘/’, ‘-‘) AS [YYYY-MM-DD]
 2011-06-08
YYYY-M-D SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + ‘-‘ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M-D] 2011-6-8
YY-M-D SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + ‘-‘ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M-D] 11-6-8
M-D-YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-D-YYYY] 6-8-2011
M-D-YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-D-YY] 6-8-11
D-M-YYYY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D-M-YYYY] 8-6-2011
D-M-YY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D-M-YY] 8-6-11
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), SYSDATETIME(), 20), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), SYSDATETIME(), 120), 3, 5) AS [YY-MM]		 11-06
YYYY-MM SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM] 2011-06
YY-M SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + ‘-‘ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M] 11-6
YYYY-M SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + ‘-‘ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M] 2011-6
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 4, 5) AS [MM-YY]		 06-11
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY] 06-2011
M-YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY] 6-11
M-YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY] 6-2011
MM-DD SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD] 06-08
DD-MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM] 08-06
M-D SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D] 6-8
D-M SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘-‘ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M] 8-6
M/D/YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY] 6/8/2011
M/D/YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/D/YY] 6/8/11
D/M/YYYY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D/M/YYYY] 8/6/2011
D/M/YY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D/M/YY] 8/6/11
YYYY/M/D SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + ‘/’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M/D] 2011/6/8
YY/M/D SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + ‘/’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M/D] 11/6/8
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY] 06/11
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY] 06/2011
M/YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY] 6/11
M/YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY] 6/2011
YY/MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM] 11/06
YYYY/MM SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM] 2011/06
YY/M SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + ‘/’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M] 11/6
YYYY/M SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + ‘/’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M] 2011/6
MM/DD SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD] 06/08
DD/MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM] 08/06
M/D SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D] 6/8
D/M SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘/’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M] 8/6
MM.DD.YYYY SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), ‘/’, ‘.’) AS [MM.DD.YYYY] 06.08.2011
MM.DD.YY SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), ‘/’, ‘.’) AS [MM.DD.YY] 06.08.11
M.D.YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.D.YYYY] 6.8.2011
M.D.YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.D.YY] 6.8.11
DD.MM.YYYY SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] 08.06.2011
DD.MM.YY SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY] 08.06.11
D.M.YYYY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D.M.YYYY] 8.6.2011
D.M.YY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D.M.YY] 8.6.11
YYYY.M.D SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + ‘.’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M.D] 2011.6.8
YY.M.D SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + ‘.’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M.D] 11.6.8
MM.YYYY SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY] 06.2011
MM.YY SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY] 06.11
M.YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY] 6.2011
M.YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + ‘.’ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY] 6.11
YYYY.MM SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM] 2011.06
YY.MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM] 11.06
YYYY.M SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + ‘.’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M] 2011.6
YY.M SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + ‘.’ + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M] 11.6
MM.DD SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD] 06.08
DD.MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM] 08.06
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), ‘/’, ”) AS [MMDDYYYY] 06082011
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), ‘/’, ”) AS [MMDDYY] 060811
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), ‘/’, ”) AS [DDMMYYYY] 08062011
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), ‘/’, ”) AS [DDMMYY] 080611
MMYYYY SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), ‘/’, ”), 6) AS [MMYYYY] 062011
MMYY SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), ‘/’, ”), 4) AS [MMYY] 0611
YYYYMM SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM] 201106
YYMM SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM] 1106
Month DD, YYYY 1 SELECT DATENAME(MONTH, SYSDATETIME())+ ‘ ‘ + RIGHT(‘0’ + DATENAME(DAY, SYSDATETIME()), 2) + ‘, ‘ + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY] June 08, 2011 1
Mon YYYY 1 SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ‘ ‘ + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY] Jun 2011 1
Month YYYY 1 SELECT DATENAME(MONTH, SYSDATETIME()) + ‘ ‘ + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY] June 2011 1
DD Month 1 SELECT RIGHT(‘0’ + DATENAME(DAY, SYSDATETIME()), 2) + ‘ ‘ + DATENAME(MONTH, SYSDATETIME())  AS [DD Month] 08 June 1
Month DD 1 SELECT DATENAME(MONTH, SYSDATETIME()) + ‘ ‘ + RIGHT(‘0’ + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD] June 08 1
DD Month YY 1 SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, SYSDATETIME()) + ‘ ‘ + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY] 08 June 11 1
DD Month YYYY 1 SELECT RIGHT(‘0’ + DATENAME(DAY, SYSDATETIME()), 2) + ‘ ‘ + DATENAME(MONTH, SYSDATETIME())+ ‘ ‘ + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY] 08 June 2011 1
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY] Jun-08 1
Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY] Jun-2011 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY] 08-Jun-11 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY] 08-Jun-2011 1

1 To make the month name in upper case, simply use the UPPER string function.

From:http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx