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. |
Standard Date Formats |
Date Format |
Standard |
SQL Statement |
Sample Output |
Mon DD YYYY 1 HH:MIAM (or PM) |
Default |
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) |
Jan 1 2005 1:29PM 1 |
MM/DD/YY |
USA |
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] |
11/23/98 |
MM/DD/YYYY |
USA |
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] |
11/23/1998 |
YY.MM.DD |
ANSI |
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] |
72.01.01 |
YYYY.MM.DD |
ANSI |
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] |
1972.01.01 |
DD/MM/YY |
British/French |
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] |
19/02/72 |
DD/MM/YYYY |
British/French |
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] |
19/02/1972 |
DD.MM.YY |
German |
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] |
25.12.05 |
DD.MM.YYYY |
German |
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] |
25.12.2005 |
DD-MM-YY |
Italian |
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] |
24-01-98 |
DD-MM-YYYY |
Italian |
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] |
24-01-1998 |
DD Mon YY 1 |
- |
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] |
04 Jul 06 1 |
DD Mon YYYY 1 |
- |
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] |
04 Jul 2006 1 |
Mon DD, YY 1 |
- |
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] |
Jan 24, 98 1 |
Mon DD, YYYY 1 |
- |
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] |
Jan 24, 1998 1 |
HH:MM:SS |
- |
SELECT CONVERT(VARCHAR(8), GETDATE(), 108) |
03:24:53 |
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 |
Default + milliseconds |
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) |
Apr 28 2006 12:32:29:253PM 1 |
MM-DD-YY |
USA |
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] |
01-01-06 |
MM-DD-YYYY |
USA |
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] |
01-01-2006 |
YY/MM/DD |
- |
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] |
98/11/23 |
YYYY/MM/DD |
- |
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] |
1998/11/23 |
YYMMDD |
ISO |
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] |
980124 |
YYYYMMDD |
ISO |
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] |
19980124 |
DD Mon YYYY HH:MM:SS:MMM(24h) 1 |
Europe default + milliseconds |
SELECT CONVERT(VARCHAR(24), GETDATE(), 113) |
28 Apr 2006 00:34:55:190 1 |
HH:MI:SS:MMM(24H) |
- |
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] |
11:34:23:013 |
YYYY-MM-DD HH:MI:SS(24h) |
ODBC Canonical |
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) |
1972-01-01 13:42:24 |
YYYY-MM-DD HH:MI:SS.MMM(24h) |
ODBC Canonical (with milliseconds) |
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) |
1972-02-19 06:35:24.489 |
YYYY-MM-DDTHH:MM:SS:MMM |
ISO8601 |
SELECT CONVERT(VARCHAR(23), GETDATE(), 126) |
1998-11-23T11:25:43:250 |
DD Mon YYYY HH:MI:SS:MMMAM 1 |
Kuwaiti |
SELECT CONVERT(VARCHAR(26), GETDATE(), 130) |
28 Apr 2006 12:39:32:429AM 1 |
DD/MM/YYYY HH:MI:SS:MMMAM |
Kuwaiti |
SELECT CONVERT(VARCHAR(25), GETDATE(), 131) |
28/04/2006 12:39:32:429AM | |
Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.
Extended Date Formats |
Date Format |
SQL Statement |
Sample Output |
YY-MM-DD |
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD] |
99-01-24 |
YYYY-MM-DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD] |
1999-01-24 |
MM/YY |
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY] |
08/99 |
MM/YYYY |
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] |
12/2005 |
YY/MM |
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] |
99/08 |
YYYY/MM |
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] |
2005/12 |
Month DD, YYYY 1 |
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] |
July 04, 2006 1 |
Mon YYYY 1 |
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] |
Apr 2006 1 |
Month YYYY 1 |
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] |
February 2006 1 |
DD Month 1 |
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] |
11 September 1 |
Month DD 1 |
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] |
September 11 1 |
DD Month YY 1 |
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] |
19 February 72 1 |
DD Month YYYY 1 |
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] |
11 September 2002 1 |
MM-YY |
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY] |
12/92 |
MM-YYYY |
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] |
05-2006 |
YY-MM |
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM] |
92/12 |
YYYY-MM |
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] |
2006-05 |
MMDDYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] |
122506 |
MMDDYYYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] |
12252006 |
DDMMYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] |
240702 |
DDMMYYYY |
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] |
24072002 |
Mon-YY 1 |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] |
Sep-02 1 |
Mon-YYYY 1 |
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] |
Sep-2002 1 |
DD-Mon-YY 1 |
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] |
25-Dec-05 1 |
DD-Mon-YYYY 1 |
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] |
25-Dec-2005 1 |
1 To make the month name in upper case, simply use the UPPER string function. |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析