|
|
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.
| Date Format |
FORMAT Function |
CONVERT Function |
Sample 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/YY |
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yy') AS [MM/DD/YY] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY] |
06/08/11 |
| MM/DD/YYYY |
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy') AS [MM/DD/YYYY] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY] |
06/08/2011 |
| YY.MM.DD |
SELECT FORMAT(SYSDATETIME(), 'yy.MM.dd') AS [YY.MM.DD] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD] |
11.06.08 |
| YYYY.MM.DD |
SELECT FORMAT(SYSDATETIME(), 'yyyy.MM.dd') AS [YYYY.MM.DD] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD] |
2011.06.08 |
| DD/MM/YY |
SELECT FORMAT(SYSDATETIME(), 'dd/MM/yy') AS [DD/MM/YY] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY] |
08/06/11 |
| DD/MM/YYYY |
SELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy') AS [DD/MM/YYYY] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY] |
08/06/2011 |
| DD.MM.YY |
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY] |
08.06.11 |
| DD.MM.YYYY |
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] |
08.06.2011 |
| DD-MM-YY |
SELECT FORMAT(SYSDATETIME(), 'dd-MM-yy') AS [DD-MM-YY] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY] |
08-06-11 |
| DD-MM-YYYY |
SELECT 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 1 |
SELECT 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 1 |
SELECT 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 1 |
SELECT 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 1 |
SELECT 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:SS |
SELECT 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) 1 |
SELECT 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-YY |
SELECT FORMAT(SYSDATETIME(), 'MM-dd-yy') AS [MM-DD-YY] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY] |
06-08-11 |
| MM-DD-YYYY |
SELECT FORMAT(SYSDATETIME(), 'MM-dd-yyyy') AS [MM-DD-YYYY] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY] |
06-08-2011 |
| YY/MM/DD |
SELECT FORMAT(SYSDATETIME(), 'yy/MM/dd') AS [YY/MM/DD] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD] |
11/06/08 |
| YYYY/MM/DD |
SELECT FORMAT(SYSDATETIME(), 'yyyy/MM/dd') AS [YYYY/MM/DD] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD] |
2011/06/08 |
| YYMMDD |
SELECT FORMAT(SYSDATETIME(), 'yyMMdd') AS [YYMMDD] |
SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD] |
110608 |
| YYYYMMDD |
SELECT FORMAT(SYSDATETIME(), 'yyyyMMdd') AS [YYYYMMDD] |
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD] |
20110608 |
| DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1 |
SELECT 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 AM |
SELECT FORMAT(SYSDATETIME(), 'MM/dd/yy h:mm:ss tt') |
SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22) |
06/08/11 1:30:45 PM |
| YYYY-MM-DD |
SELECT 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.NNNNNNN |
SELECT 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:NNNNNNN |
SELECT 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 1 |
SELECT 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.NNNNNNNAM |
SELECT 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.
| Date Format |
FORMAT Function |
CONVERT Function |
Sample Output |
| YY-MM-DD |
SELECT 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-DD |
SELECT 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-D |
SELECT 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-D |
SELECT 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-YYYY |
SELECT 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-YY |
SELECT 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-YYYY |
SELECT 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-YY |
SELECT 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-MM |
SELECT 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-MM |
SELECT FORMAT(SYSDATETIME(), 'yyyy-MM') AS [YYYY-MM] |
SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM] |
2011-06 |
| YY-M |
SELECT 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-M |
SELECT 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-YY |
SELECT 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-YYYY |
SELECT FORMAT(SYSDATETIME(), 'MM-yyyy') AS [MM-YYYY] |
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY] |
06-2011 |
| M-YY |
SELECT 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-YYYY |
SELECT 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-DD |
SELECT FORMAT(SYSDATETIME(), 'MM-dd') AS [MM-DD] |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD] |
06-08 |
| DD-MM |
SELECT FORMAT(SYSDATETIME(), 'dd-MM') AS [DD-MM] |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM] |
08-06 |
| M-D |
SELECT 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-M |
SELECT 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/YYYY |
SELECT 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/YY |
SELECT 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/YYYY |
SELECT 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/YY |
SELECT 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/D |
SELECT 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/D |
SELECT 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/YY |
SELECT FORMAT(SYSDATETIME(), 'MM/yy') AS [MM/YY] |
SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY] |
06/11 |
| MM/YYYY |
SELECT FORMAT(SYSDATETIME(), 'MM/yyyy') AS [MM/YYYY] |
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY] |
06/2011 |
| M/YY |
SELECT 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/YYYY |
SELECT 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/MM |
SELECT FORMAT(SYSDATETIME(), 'yy/MM') AS [YY/MM] |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM] |
11/06 |
| YYYY/MM |
SELECT FORMAT(SYSDATETIME(), 'yyyy/MM') AS [YYYY/MM] |
SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM] |
2011/06 |
| YY/M |
SELECT 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/M |
SELECT 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/DD |
SELECT FORMAT(SYSDATETIME(), 'MM/dd') AS [MM/DD] |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD] |
06/08 |
| DD/MM |
SELECT FORMAT(SYSDATETIME(), 'dd/MM') AS [DD/MM] |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM] |
08/06 |
| M/D |
SELECT 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/M |
SELECT 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.YYYY |
SELECT 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.YY |
SELECT 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.YYYY |
SELECT 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.YY |
SELECT 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.YYYY |
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] |
08.06.2011 |
| DD.MM.YY |
SELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY] |
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY] |
08.06.11 |
| D.M.YYYY |
SELECT 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.YY |
SELECT 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.D |
SELECT 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.D |
SELECT 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.YYYY |
SELECT FORMAT(SYSDATETIME(), 'MM.yyyy') AS [MM.YYYY] |
SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY] |
06.2011 |
| MM.YY |
SELECT FORMAT(SYSDATETIME(), 'MM.yy') AS [MM.YY] |
SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY] |
06.11 |
| M.YYYY |
SELECT 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.YY |
SELECT 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.MM |
SELECT FORMAT(SYSDATETIME(), 'yyyy.MM') AS [YYYY.MM] |
SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM] |
2011.06 |
| YY.MM |
SELECT FORMAT(SYSDATETIME(), 'yy.MM') AS [YY.MM] |
SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM] |
11.06 |
| YYYY.M |
SELECT 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.M |
SELECT 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.DD |
SELECT FORMAT(SYSDATETIME(), 'MM.dd') AS [MM.DD] |
SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD] |
06.08 |
| DD.MM |
SELECT FORMAT(SYSDATETIME(), 'dd.MM') AS [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 FORMAT(SYSDATETIME(), 'ddMMyyyy') AS [DDMMYYYY] |
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY] |
08062011 |
| DDMMYY |
SELECT FORMAT(SYSDATETIME(), 'ddMMyy') AS [DDMMYY] |
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY] |
080611 |
| MMYYYY |
SELECT FORMAT(SYSDATETIME(), 'MMyyyy') AS [MMYYYY] |
SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY] |
062011 |
| MMYY |
SELECT FORMAT(SYSDATETIME(), 'MMyy') AS [MMYY] |
SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY] |
0611 |
| YYYYMM |
SELECT FORMAT(SYSDATETIME(), 'yyyyMM') AS [YYYYMM] |
SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM] |
201106 |
| YYMM |
SELECT FORMAT(SYSDATETIME(), 'yyMM') AS [YYMM] |
SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM] |
1106 |
| Month DD, YYYY 1 |
SELECT 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 1 |
SELECT FORMAT(SYSDATETIME(), 'MMM yyyy') AS [Mon YYYY] |
SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY] |
Jun 2011 1 |
| Month YYYY 1 |
SELECT FORMAT(SYSDATETIME(), 'MMMM yyyy') AS [Month YYYY] |
SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS
[Month YYYY]
|
June 2011 1 |
| DD Month 1 |
SELECT 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 1 |
SELECT 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 1 |
SELECT 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 11 1 |
| DD Month YYYY 1 |
SELECT 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 1 |
SELECT FORMAT(SYSDATETIME(), 'MMM-yy') AS [Mon-YY] |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS
[Mon-YY] |
Jun-08 1 |
| Mon-YYYY 1 |
SELECT FORMAT(SYSDATETIME(), 'MMM-yyyy') AS [Mon-YYYY] |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS
[Mon-YYYY] |
Jun-2011 1 |
| DD-Mon-YY 1 |
SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yy') AS [DD-Mon-YY] |
SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY] |
08-Jun-11 1 |
| DD-Mon-YYYY 1 |
SELECT 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.
|