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.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.

TablePlus for MS SQL Server