{"id":64390,"date":"2014-12-09T15:59:54","date_gmt":"2014-12-09T20:59:54","guid":{"rendered":"https:\/\/mkncreations.com\/site\/?p=64390"},"modified":"2014-12-09T16:02:26","modified_gmt":"2014-12-09T21:02:26","slug":"tsql-date-formats","status":"publish","type":"post","link":"https:\/\/mkncreations.com\/site\/2014\/12\/tsql-date-formats\/","title":{"rendered":"TSQL – Date Formats"},"content":{"rendered":"

Here is a list of the different date formats that come standard in SQL Server as part of the CONVERT function.
\n<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Standard Date Formats<\/td>\n<\/tr>\n
Date Format<\/td>\nStandard<\/td>\nSQL Statement<\/td>\nSample Output<\/td>\n<\/tr>\n
Mon DD YYYY 1<\/sup><\/p>\n

HH:MIAM (or PM)<\/td>\n

Default<\/td>\nSELECT CONVERT(VARCHAR(20), GETDATE(), 100)<\/td>\nJan 1 2005 1:29PM 1<\/sup><\/td>\n<\/tr>\n
MM\/DD\/YY<\/td>\nUSA<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM\/DD\/YY]<\/td>\n11\/23\/98<\/td>\n<\/tr>\n
MM\/DD\/YYYY<\/td>\nUSA<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM\/DD\/YYYY]<\/td>\n11\/23\/1998<\/td>\n<\/tr>\n
YY.MM.DD<\/td>\nANSI<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]<\/td>\n72.01.01<\/td>\n<\/tr>\n
YYYY.MM.DD<\/td>\nANSI<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]<\/td>\n1972.01.01<\/td>\n<\/tr>\n
DD\/MM\/YY<\/td>\nBritish\/French<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD\/MM\/YY]<\/td>\n19\/02\/72<\/td>\n<\/tr>\n
DD\/MM\/YYYY<\/td>\nBritish\/French<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD\/MM\/YYYY]<\/td>\n19\/02\/1972<\/td>\n<\/tr>\n
DD.MM.YY<\/td>\nGerman<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]<\/td>\n25.12.05<\/td>\n<\/tr>\n
DD.MM.YYYY<\/td>\nGerman<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]<\/td>\n25.12.2005<\/td>\n<\/tr>\n
DD-MM-YY<\/td>\nItalian<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]<\/td>\n24-01-98<\/td>\n<\/tr>\n
DD-MM-YYYY<\/td>\nItalian<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]<\/td>\n24-01-1998<\/td>\n<\/tr>\n
DD Mon YY 1<\/sup><\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]<\/td>\n04 Jul 06 1<\/sup><\/td>\n<\/tr>\n
DD Mon YYYY 1<\/sup><\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]<\/td>\n04 Jul 2006 1<\/sup><\/td>\n<\/tr>\n
Mon DD, YY 1<\/sup><\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]<\/td>\nJan 24, 98 1<\/sup><\/td>\n<\/tr>\n
Mon DD, YYYY 1<\/sup><\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]<\/td>\nJan 24, 1998 1<\/sup><\/td>\n<\/tr>\n
HH:MM:SS<\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 108)<\/td>\n03:24:53<\/td>\n<\/tr>\n
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1<\/sup><\/td>\nDefault +<\/p>\n

milliseconds<\/td>\n

SELECT CONVERT(VARCHAR(26), GETDATE(), 109)<\/td>\nApr 28 2006 12:32:29:253PM 1<\/sup><\/td>\n<\/tr>\n
MM-DD-YY<\/td>\nUSA<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]<\/td>\n01-01-06<\/td>\n<\/tr>\n
MM-DD-YYYY<\/td>\nUSA<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]<\/td>\n01-01-2006<\/td>\n<\/tr>\n
YY\/MM\/DD<\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY\/MM\/DD]<\/td>\n98\/11\/23<\/td>\n<\/tr>\n
YYYY\/MM\/DD<\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY\/MM\/DD]<\/td>\n1998\/11\/23<\/td>\n<\/tr>\n
YYMMDD<\/td>\nISO<\/td>\nSELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]<\/td>\n980124<\/td>\n<\/tr>\n
YYYYMMDD<\/td>\nISO<\/td>\nSELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]<\/td>\n19980124<\/td>\n<\/tr>\n
DD Mon YYYY HH:MM:SS:MMM(24h) 1<\/sup><\/td>\nEurope default + milliseconds<\/td>\nSELECT CONVERT(VARCHAR(24), GETDATE(), 113)<\/td>\n28 Apr 2006 00:34:55:190 1<\/sup><\/td>\n<\/tr>\n
HH:MI:SS:MMM(24H)<\/td>\n–<\/td>\nSELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]<\/td>\n11:34:23:013<\/td>\n<\/tr>\n
YYYY-MM-DD HH:MI:SS(24h)<\/td>\nODBC Canonical<\/td>\nSELECT CONVERT(VARCHAR(19), GETDATE(), 120)<\/td>\n1972-01-01 13:42:24<\/td>\n<\/tr>\n
YYYY-MM-DD HH:MI:SS.MMM(24h)<\/td>\nODBC Canonical<\/p>\n

(with milliseconds)<\/td>\n

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)<\/td>\n1972-02-19 06:35:24.489<\/td>\n<\/tr>\n
YYYY-MM-DDTHH:MM:SS:MMM<\/td>\nISO8601<\/td>\nSELECT CONVERT(VARCHAR(23), GETDATE(), 126)<\/td>\n1998-11-23T11:25:43:250<\/td>\n<\/tr>\n
DD Mon YYYY HH:MI:SS:MMMAM 1<\/sup><\/td>\nKuwaiti<\/td>\nSELECT CONVERT(VARCHAR(26), GETDATE(), 130)<\/td>\n28 Apr 2006 12:39:32:429AM 1<\/sup><\/td>\n<\/tr>\n
DD\/MM\/YYYY HH:MI:SS:MMMAM<\/td>\nKuwaiti<\/td>\nSELECT CONVERT(VARCHAR(25), GETDATE(), 131)<\/td>\n28\/04\/2006 12:39:32:429AM<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 
\n 
\nHere are some more date formats that does not come standard in SQL Server as part of the CONVERT function.<\/strong>
\n <\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Extended Date Formats<\/td>\n<\/tr>\n
Date Format<\/td>\nSQL Statement<\/td>\nSample Output<\/td>\n<\/tr>\n
YY-MM-DD<\/td>\n\n
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]<\/div>\n
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘\/’, ‘-‘) AS [YY-MM-DD]<\/div>\n<\/td>\n
99-01-24<\/td>\n<\/tr>\n
YYYY-MM-DD<\/td>\n\n
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]<\/div>\n
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘\/’, ‘-‘) AS [YYYY-MM-DD]<\/div>\n<\/td>\n
1999-01-24<\/td>\n<\/tr>\n
MM\/YY<\/td>\nSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM\/YY]<\/p>\n

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM\/YY]<\/td>\n

08\/99<\/td>\n<\/tr>\n
MM\/YYYY<\/td>\nSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM\/YYYY]<\/td>\n12\/2005<\/td>\n<\/tr>\n
YY\/MM<\/td>\nSELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY\/MM]<\/td>\n99\/08<\/td>\n<\/tr>\n
YYYY\/MM<\/td>\nSELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY\/MM]<\/td>\n2005\/12<\/td>\n<\/tr>\n
Month DD, YYYY 1<\/sup><\/td>\nSELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9)
\nAS [Month DD, YYYY]<\/td>\n		July 04, 2006 1<\/sup><\/td>\n<\/tr>\n
Mon YYYY 1<\/sup><\/td>\nSELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]<\/td>\nApr 2006 1<\/sup><\/td>\n<\/tr>\n
Month YYYY 1<\/sup><\/td>\nSELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS
\n[Month YYYY]<\/td>\n		February 2006 1<\/sup><\/td>\n<\/tr>\n
DD Month 1<\/sup><\/td>\nSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS
\n[DD Month]<\/td>\n		11 September 1<\/sup><\/td>\n<\/tr>\n
Month DD 1<\/sup><\/td>\nSELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS
\n[Month DD]<\/td>\n		September 11 1<\/sup><\/td>\n<\/tr>\n
DD Month YY 1<\/sup><\/td>\nSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘
\n+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]<\/td>\n		19 February 72 1<\/sup><\/td>\n<\/tr>\n
DD Month YYYY 1<\/sup><\/td>\nSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘
\n+ CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]<\/td>\n		11 September 2002 1<\/sup><\/td>\n<\/tr>\n
MM-YY<\/td>\nSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]<\/p>\n

SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]<\/td>\n

12\/92<\/td>\n<\/tr>\n
MM-YYYY<\/td>\nSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]<\/td>\n05-2006<\/td>\n<\/tr>\n
YY-MM<\/td>\nSELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]<\/p>\n

SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]<\/td>\n

92\/12<\/td>\n<\/tr>\n
YYYY-MM<\/td>\nSELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]<\/td>\n2006-05<\/td>\n<\/tr>\n
MMDDYY<\/td>\nSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘\/’, ”) AS [MMDDYY]<\/td>\n122506<\/td>\n<\/tr>\n
MMDDYYYY<\/td>\nSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘\/’, ”) AS [MMDDYYYY]<\/td>\n12252006<\/td>\n<\/tr>\n
DDMMYY<\/td>\nSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘\/’, ”) AS [DDMMYY]<\/td>\n240702<\/td>\n<\/tr>\n
DDMMYYYY<\/td>\nSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘\/’, ”) AS [DDMMYYYY]<\/td>\n24072002<\/td>\n<\/tr>\n
Mon-YY 1<\/sup><\/td>\nSELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS
\n[Mon-YY]<\/td>\n		Sep-02 1<\/sup><\/td>\n<\/tr>\n
Mon-YYYY 1<\/sup><\/td>\nSELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS
\n[Mon-YYYY]<\/td>\n		Sep-2002 1<\/sup><\/td>\n<\/tr>\n
DD-Mon-YY 1<\/sup><\/td>\nSELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY]<\/td>\n25-Dec-05 1<\/sup><\/td>\n<\/tr>\n
DD-Mon-YYYY 1<\/sup><\/td>\nSELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY]<\/td>\n25-Dec-2005 1<\/sup><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 
\n1<\/sup> To make the month name in upper case, simply use the UPPER string function.<\/em><\/strong><\/p>\n

Source:<\/em><\/strong> http:\/\/www.sql-server-helper.com\/tips\/date-formats.aspx<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Here is a list of the different date formats that come standard in SQL Server as part of the CONVERT function.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12],"tags":[44,46,45,79,21,50,78,49],"class_list":["post-64390","post","type-post","status-publish","format-standard","hentry","category-how_to","category-sql","tag-date","tag-dateformat","tag-format","tag-microsoft","tag-ms","tag-sql-2","tag-sql2008","tag-tsql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/64390"}],"collection":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/comments?post=64390"}],"version-history":[{"count":18,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/64390\/revisions"}],"predecessor-version":[{"id":64419,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/posts\/64390\/revisions\/64419"}],"wp:attachment":[{"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/media?parent=64390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/categories?post=64390"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mkncreations.com\/site\/wp-json\/wp\/v2\/tags?post=64390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}