lunes, 5 de enero de 2015

SQL Server 2012 Date Formats Using the FORMAT Function and CONVERT Function

SQL Server

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx




SQL Server 2012 Date Formats Using the FORMAT Function and CONVERT Function

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 and the corresponding syntax using the new SQL Server 2012 FORMAT string function. Following the standard date formats are some extended date formats that are often asked by SQL Server developers, together with the corresponding syntax as well using the new SQL Server 2012 FORMAT string function.

It is worth to note that the outputs 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. 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 CONVERT Date Formats
Date FormatFORMAT FunctionCONVERT FunctionSample Output
Mon DD YYYY 1
HH:MIAM (or PM)		SELECT FORMAT(SYSDATETIME(), 'Mon d yyyy h:mmtt')SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)Jun 8 2011 1:30PM 1
MM/DD/YYSELECT FORMAT(SYSDATETIME(), 'MM/dd/yy') AS [MM/DD/YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]06/08/11
MM/DD/YYYYSELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy') AS [MM/DD/YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]06/08/2011
YY.MM.DDSELECT FORMAT(SYSDATETIME(), 'yy.MM.dd') AS [YY.MM.DD]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]11.06.08
YYYY.MM.DDSELECT FORMAT(SYSDATETIME(), 'yyyy.MM.dd') AS [YYYY.MM.DD]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]2011.06.08
DD/MM/YYSELECT FORMAT(SYSDATETIME(), 'dd/MM/yy') AS [DD/MM/YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]08/06/11
DD/MM/YYYYSELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy') AS [DD/MM/YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]08/06/2011
DD.MM.YYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
DD.MM.YYYYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD-MM-YYSELECT FORMAT(SYSDATETIME(), 'dd-MM-yy') AS [DD-MM-YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]08-06-11
DD-MM-YYYYSELECT FORMAT(SYSDATETIME(), 'dd-MM-yyyy') AS [DD-MM-YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]08-06-2011
DD Mon YY 1SELECT FORMAT(SYSDATETIME(), 'dd MMM yy') AS [DD MON YY]SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]08 Jun 11 1
DD Mon YYYY 1SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy') AS [DD MON YYYY]SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]08 Jun 2011 1
Mon DD, YY 1SELECT FORMAT(SYSDATETIME(), 'MMM dd, yy') AS [Mon DD, YY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]Jun 08, 11 1
Mon DD, YYYY 1SELECT FORMAT(SYSDATETIME(), 'MMM dd, yyyy') AS [Mon DD, YYYY]SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]Jun 08, 2011 1
HH:MM:SSSELECT FORMAT(SYSDATETIME(), '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) 1SELECT FORMAT(SYSDATETIME(), 'MMM d yyyy h:mm:ss.ffffffftt')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)		Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YYSELECT FORMAT(SYSDATETIME(), 'MM-dd-yy') AS [MM-DD-YY]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]06-08-11
MM-DD-YYYYSELECT FORMAT(SYSDATETIME(), 'MM-dd-yyyy') AS [MM-DD-YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]06-08-2011
YY/MM/DDSELECT FORMAT(SYSDATETIME(), 'yy/MM/dd') AS [YY/MM/DD]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]11/06/08
YYYY/MM/DDSELECT FORMAT(SYSDATETIME(), 'yyyy/MM/dd') AS [YYYY/MM/DD]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]2011/06/08
YYMMDDSELECT FORMAT(SYSDATETIME(), 'yyMMdd') AS [YYMMDD]SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]110608
YYYYMMDDSELECT FORMAT(SYSDATETIME(), 'yyyyMMdd') AS [YYYYMMDD]SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy HH:mm:ss.fffffff')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 FORMAT(SYSDATETIME(), 'HH:mm:ss.fffffff') AS [HH:MI:SS:MMM(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)SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss')SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff')SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AMSELECT FORMAT(SYSDATETIME(), 'MM/dd/yy h:mm:ss tt')SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)06/08/11 1:30:45 PM
YYYY-MM-DDSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)2011-06-091
HH:MI:SS (24h)SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss')SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNNSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNNSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-ddTHH:mm:ss.fffffff')SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy h:mm:ss.ffffffftt')SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAMSELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy h:mm:ss.ffffffftt')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, together with the corresponding FORMAT function.

Extended Date Formats
Date FormatFORMAT FunctionCONVERT FunctionSample Output
YY-MM-DDSELECT FORMAT(SYSDATETIME(), 'yy-MM-dd') AS [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-DDSELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd') AS [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-DSELECT FORMAT(SYSDATETIME(), 'yyyy-M-d') AS [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-DSELECT FORMAT(SYSDATETIME(), 'yy-M-d') AS [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-YYYYSELECT FORMAT(SYSDATETIME(), 'M-d-yyyy') AS [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-YYSELECT FORMAT(SYSDATETIME(), 'M-d-yy') AS [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-YYYYSELECT FORMAT(SYSDATETIME(), 'd-M-yyyy') AS [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-YYSELECT FORMAT(SYSDATETIME(), 'd-M-yy') AS [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-MMSELECT FORMAT(SYSDATETIME(), 'yy-MM') AS [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-MMSELECT FORMAT(SYSDATETIME(), 'yyyy-MM') AS [YYYY-MM]SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM]2011-06
YY-MSELECT FORMAT(SYSDATETIME(), 'yy-M') AS [YY-M]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M]11-6
YYYY-MSELECT FORMAT(SYSDATETIME(), 'yyyy-M') AS [YYYY-M]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M]2011-6
MM-YYSELECT FORMAT(SYSDATETIME(), 'MM-yy') AS [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-YYYYSELECT FORMAT(SYSDATETIME(), 'MM-yyyy') AS [MM-YYYY]SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY]06-2011
M-YYSELECT FORMAT(SYSDATETIME(), 'M-yy') AS [M-YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY]6-11
M-YYYYSELECT FORMAT(SYSDATETIME(), 'M-yyyy') AS [M-YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY]6-2011
MM-DDSELECT FORMAT(SYSDATETIME(), 'MM-dd') AS [MM-DD]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD]06-08
DD-MMSELECT FORMAT(SYSDATETIME(), 'dd-MM') AS [DD-MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM]08-06
M-DSELECT FORMAT(SYSDATETIME(), 'M-d') AS [M-D]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D]6-8
D-MSELECT FORMAT(SYSDATETIME(), 'd-M') AS [D-M]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M]8-6
M/D/YYYYSELECT FORMAT(SYSDATETIME(), 'M/d/yyyy') AS [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/YYSELECT FORMAT(SYSDATETIME(), 'M/d/yy') AS [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/YYYYSELECT FORMAT(SYSDATETIME(), 'd/M/yyyy') AS [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/YYSELECT FORMAT(SYSDATETIME(), 'd/M/yy') AS [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/DSELECT FORMAT(SYSDATETIME(), 'yyyy/M/d') AS [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/DSELECT FORMAT(SYSDATETIME(), 'yy/M/d') AS [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/YYSELECT FORMAT(SYSDATETIME(), 'MM/yy') AS [MM/YY]SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY]06/11
MM/YYYYSELECT FORMAT(SYSDATETIME(), 'MM/yyyy') AS [MM/YYYY]SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY]06/2011
M/YYSELECT FORMAT(SYSDATETIME(), 'M/yy') AS [M/YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY]6/11
M/YYYYSELECT FORMAT(SYSDATETIME(), 'M/yyyy') AS [M/YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY]6/2011
YY/MMSELECT FORMAT(SYSDATETIME(), 'yy/MM') AS [YY/MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM]11/06
YYYY/MMSELECT FORMAT(SYSDATETIME(), 'yyyy/MM') AS [YYYY/MM]SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM]2011/06
YY/MSELECT FORMAT(SYSDATETIME(), 'yy/M') AS [YY/M]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M]11/6
YYYY/MSELECT FORMAT(SYSDATETIME(), 'yyyy/M') AS [YYYY/M]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M]2011/6
MM/DDSELECT FORMAT(SYSDATETIME(), 'MM/dd') AS [MM/DD]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD]06/08
DD/MMSELECT FORMAT(SYSDATETIME(), 'dd/MM') AS [DD/MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM]08/06
M/DSELECT FORMAT(SYSDATETIME(), 'M/d') AS [M/D]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D]6/8
D/MSELECT FORMAT(SYSDATETIME(), 'd/M') AS [D/M]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M]8/6
MM.DD.YYYYSELECT FORMAT(SYSDATETIME(), 'MM.dd.yyyy') AS [MM.DD.YYYY]SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY]06.08.2011
MM.DD.YYSELECT FORMAT(SYSDATETIME(), 'MM.dd.yy') AS [MM.DD.YY]SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY]06.08.11
M.D.YYYYSELECT FORMAT(SYSDATETIME(), 'M.d.yyyy') AS [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.YYSELECT FORMAT(SYSDATETIME(), 'M.d.yy') AS [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.YYYYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD.MM.YYSELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY]SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
D.M.YYYYSELECT FORMAT(SYSDATETIME(), 'd.M.yyyy') AS [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.YYSELECT FORMAT(SYSDATETIME(), 'd.M.yy') AS [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.DSELECT FORMAT(SYSDATETIME(), 'yyyy.M.d') AS [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.DSELECT FORMAT(SYSDATETIME(), 'yy.M.d') AS [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.YYYYSELECT FORMAT(SYSDATETIME(), 'MM.yyyy') AS [MM.YYYY]SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY]06.2011
MM.YYSELECT FORMAT(SYSDATETIME(), 'MM.yy') AS [MM.YY]SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY]06.11
M.YYYYSELECT FORMAT(SYSDATETIME(), 'M.yyyy') AS [M.YYYY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY]6.2011
M.YYSELECT FORMAT(SYSDATETIME(), 'M.yy') AS [M.YY]SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY]6.11
YYYY.MMSELECT FORMAT(SYSDATETIME(), 'yyyy.MM') AS [YYYY.MM]SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM]2011.06
YY.MMSELECT FORMAT(SYSDATETIME(), 'yy.MM') AS [YY.MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM]11.06
YYYY.MSELECT FORMAT(SYSDATETIME(), 'yyyy.M') AS [YYYY.M]SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M]2011.6
YY.MSELECT FORMAT(SYSDATETIME(), 'yy.M') AS [YY.M]SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M]11.6
MM.DDSELECT FORMAT(SYSDATETIME(), 'MM.dd') AS [MM.DD]SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD]06.08
DD.MMSELECT FORMAT(SYSDATETIME(), 'dd.MM') AS [DD.MM]SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM]08.06
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '') AS [MMDDYYYY]06082011
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') AS [MMDDYY]060811
DDMMYYYYSELECT FORMAT(SYSDATETIME(), 'ddMMyyyy') AS [DDMMYYYY]SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY]08062011
DDMMYYSELECT FORMAT(SYSDATETIME(), 'ddMMyy') AS [DDMMYY]SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY]080611
MMYYYYSELECT FORMAT(SYSDATETIME(), 'MMyyyy') AS [MMYYYY]SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY]062011
MMYYSELECT FORMAT(SYSDATETIME(), 'MMyy') AS [MMYY]SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY]0611
YYYYMMSELECT FORMAT(SYSDATETIME(), 'yyyyMM') AS [YYYYMM]SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM]201106
YYMMSELECT FORMAT(SYSDATETIME(), 'yyMM') AS [YYMM]SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM]1106
Month DD, YYYY 1SELECT FORMAT(SYSDATETIME(), 'MMMM dd, yyyy') AS [Month DD, YYYY]SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY]June 08, 2011 1
Mon YYYY 1SELECT FORMAT(SYSDATETIME(), 'MMM yyyy') AS [Mon YYYY]SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY]Jun 2011 1
Month YYYY1SELECT FORMAT(SYSDATETIME(), 'MMMM yyyy') AS [Month YYYY]SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY]June 20111
DD Month 1SELECT FORMAT(SYSDATETIME(), 'dd MMMM') AS [DD Month]SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS [DD Month]08 June 1
Month DD 1SELECT FORMAT(SYSDATETIME(), 'MMMM dd') AS [Month DD]SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD]June 08 1
DD Month YY 1SELECT FORMAT(SYSDATETIME(), 'dd MMMM yy') AS [DD Month YY]SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ' ' + DATENAME(MM, SYSDATETIME()) + ' ' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY]08 June 111
DD Month YYYY 1SELECT FORMAT(SYSDATETIME(), 'dd MMMM yyyy') AS [DD Month YYYY]SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY]08 June 2011 1
Mon-YY 1SELECT FORMAT(SYSDATETIME(), 'MMM-yy') AS [Mon-YY]SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS [Mon-YY]Jun-08 1
Mon-YYYY 1SELECT FORMAT(SYSDATETIME(), 'MMM-yyyy') AS [Mon-YYYY]SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY]Jun-2011 1
DD-Mon-YY1SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yy') AS [DD-Mon-YY]SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY]08-Jun-111
DD-Mon-YYYY 1SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yyyy') AS [DD-Mon-YYYY]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.