|
|
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.
| 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.
| 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.
|