SQL Server Date Format Cheatsheet
In MS SQL Server, you can use the CONVERT()
function to converts an expression from one data type to another data type. When it comes to converting datetime value to character, there are so many formatting styles for the output.
In this post, we are going to introduce all the date formats, as long as the corresponding CONVERT()
statement in SQL Server.
General syntax to convert a datetime or smalldatetime value to character:
SELECT CONVERT(VARCHAR(n), GETDATE(), style)
And the Date and Time style can be chosen from the list below:
Style | Standard | CONVERT statement | Output |
---|---|---|---|
100 | Default for datetime and smalldatetime | SELECT CONVERT(VARCHAR(20), GETDATE(), 100) | mon dd yyyy hh:miAM (or PM) |
101 | U.S. | SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] | mm/dd/yyyy |
102 | ANSI | SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] | yyyy.mm.dd |
103 | British/French | SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] | dd/mm/yyyy |
104 | German | SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] | dd.mm.yyyy |
105 | Italian | SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] | dd-mm-yyyy |
106 | - | SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] | dd mon yyyy |
107 | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] | Mon dd, yyyy |
108 | - | SELECT CONVERT(VARCHAR(8), GETDATE(), 108) | hh:mi:ss |
109 | Default + milliseconds | SELECT CONVERT(VARCHAR(26), GETDATE(), 109) | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
110 | USA | SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] | mm-dd-yyyy |
111 | JAPAN | SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] | yyyy/mm/dd |
112 | ISO | SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] | yyyymmdd |
113 | Europe default + milliseconds | SELECT CONVERT(VARCHAR(24), GETDATE(), 113) | dd mon yyyy hh:mi:ss:mmm(24h) |
114 | - | SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] | hh:mi:ss:mmm(24h) |
120 | ODBC canonical | SELECT CONVERT(VARCHAR(19), GETDATE(), 120) | yyyy-mm-dd hh:mi:ss(24h) |
121 | ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset | SELECT CONVERT(VARCHAR(23), GETDATE(), 121) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
126 | ISO8601 | SELECT CONVERT(VARCHAR(23), GETDATE(), 126) | yyyy-mm-ddThh:mi:ss.mmm (no spaces) |
130 | Hijri | SELECT CONVERT(VARCHAR(26), GETDATE(), 130) | dd mon yyyy hh:mi:ss:mmmAM |
131 | Hijra | SELECT CONVERT(VARCHAR(25), GETDATE(), 131) | dd/mm/yyyy hh:mi:ss:mmmAM |
Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple databases including SQL Server, MySQL, PostgreSQL, SQLite, etc. And it’s free to use for as long as you need it to.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS.