Syntax

To convert a varchar string value to a datetime value using the CONVERT function in SQL Server, here is the general syntax:

CONVERT(datetime, expression [, style ])

That statement will convert the expression from varchar to datetime value using the specified style.

Here is the list of style values that you can use in the CONVERT statement:

Style Standard Output
100 Default for datetime and smalldatetime mon dd yyyy hh:miAM (or PM)
101 U.S. mm/dd/yyyy
102 ANSI yyyy.mm.dd
103 British/French dd/mm/yyyy
104 German dd.mm.yyyy
105 Italian dd-mm-yyyy
106 - dd mon yyyy
107 - Mon dd, yyyy
108 - hh:mi:ss
109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 USA mm-dd-yyyy
111 JAPAN yyyy/mm/dd
112 ISO yyyymmdd
113 Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
114 - hh:mi:ss:mmm(24h)
120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
121 ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset yyyy-mm-dd hh:mi:ss.mmm(24h)
126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
130 Hijri dd mon yyyy hh:mi:ss:mmmAM
131 Hijra dd/mm/yyyy hh:mi:ss:mmmAM

You can also use the CAST function:

CAST(expression AS datetime)

Read more on how to use the CAST() function.

Example

SELECT convert(datetime, '09/10/2019', 101);

With the style 101 (mm/dd/yyyy), the string value 09/10/2019 is now a datetime value.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode