Tuesday, 2 February 2016

SQL Server Standard Date Formats


Date FormatStandardSQL StatementSample Output
Mon DD YYYY 1
HH:MIAM (or PM)
DefaultSELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)Jun 8 2011 1:30PM 1
MM/DD/YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]06/08/11
MM/DD/YYYYUSASELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]06/08/2011
YY.MM.DDANSISELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]11.06.08
YYYY.MM.DDANSISELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]2011.06.08
DD/MM/YYBritish/FrenchSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]08/06/11
DD/MM/YYYYBritish/FrenchSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]08/06/2011
DD.MM.YYGermanSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
DD.MM.YYYYGermanSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD-MM-YYItalianSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]08-06-11
DD-MM-YYYYItalianSELECT 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) 1Default + 
nanoseconds
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)
Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]06-08-11
MM-DD-YYYYUSASELECT 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
YYMMDDISOSELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]110608
YYYYMMDDISOSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1Europe default + nanosecondsSELECT 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 CanonicalSELECT 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:NNNNNNNISO8601SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1KuwaitiSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAMKuwaitiSELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)08/06/2011 1:30:45.9428675PM





Extended Date Formats
Date FormatSQL StatementSample 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-DSELECT 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 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 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 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 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 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 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 CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM]2011-06
YY-MSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M]11-6
YYYY-MSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M]2011-6
MM-YYSELECT 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 RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY]06-2011
M-YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY]6-11
M-YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY]6-2011
MM-DDSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD]06-08
DD-MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM]08-06
M-DSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D]6-8
D-MSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M]8-6
M/D/YYYYSELECT 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 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 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 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 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 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 RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY]06/11
MM/YYYYSELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY]06/2011
M/YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY]6/11
M/YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY]6/2011
YY/MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM]11/06
YYYY/MMSELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM]2011/06
YY/MSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M]11/6
YYYY/MSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M]2011/6
MM/DDSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD]06/08
DD/MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM]08/06
M/DSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D]6/8
D/MSELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M]8/6
MM.DD.YYYYSELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY]06.08.2011
MM.DD.YYSELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY]06.08.11
M.D.YYYYSELECT 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 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 CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD.MM.YYSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
D.M.YYYYSELECT 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 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 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 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 RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY]06.2011
MM.YYSELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY]06.11
M.YYYYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY]6.2011
M.YYSELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY]6.11
YYYY.MMSELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM]2011.06
YY.MMSELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM]11.06
YYYY.MSELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M]2011.6
YY.MSELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M]11.6
MM.DDSELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD]06.08
DD.MMSELECT 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 REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY]08062011
DDMMYYSELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY]080611
MMYYYYSELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY]062011
MMYYSELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY]0611
YYYYMMSELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM]201106
YYMMSELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM]1106
Month DD, YYYY 1SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY]June 08, 20111
Mon YYYY 1SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY]Jun 2011 1
Month YYYY1SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY]June 2011 1
DD Month 1SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS [DD Month]08 June 1
Month DD 1SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD]June 08 1
DD Month YY 1SELECT 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 1SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY]08 June 2011 1
Mon-YY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS [Mon-YY]Jun-08 1
Mon-YYYY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY]Jun-2011 1
DD-Mon-YY1SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY]08-Jun-11 1
DD-Mon-YYYY 1SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ' ', '-') AS [DD-Mon-YYYY]08-Jun-2011 1

No comments:

Post a Comment