Wednesday, March 13, 2013

SQL Server 2008 Date Format



                                                                                                                                                                      Standard Date Formats  
Date Format
SQL Statement
Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)
Jun 8 2011 1:30PM 1
MM/DD/YY
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]
06/08/11
MM/DD/YYYY
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]
06/08/2011
YY.MM.DD
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]
11.06.08
YYYY.MM.DD
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]
2011.06.08
DD/MM/YY
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]
08/06/11
DD/MM/YYYY
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]
08/06/2011
DD.MM.YY
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]
08.06.11
DD.MM.YYYY
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]
08.06.2011
DD-MM-YY
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]
08-06-11
DD-MM-YYYY
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
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)
Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YY
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]
06-08-11
MM-DD-YYYY
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
SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]
110608
YYYYMMDD
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]
20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1
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)
SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)
2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)
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
SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)
2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)
08 Jun 2011 1:30:45.9428675PM1


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 YYYY1
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-YY1
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

Post a Comment