If you need to convert a string into a date/time value in SQL Server, you have a number of options. In this post I outline six T-SQL functions that allow you to do this.

The six functions are:

• CAST()
• CONVERT()
• PARSE()
• TRY_CAST()
• TRY_CONVERT()
• TRY_PARSE()

Below are example of how you can use these functions to convert a string to a date/time data type.

The CAST() Function

In this example, we convert the string into a date data type (we specify this by using date as the second argument).

SELECT CAST('20 Dec 2018' AS date) AS Result;

Result:

+------------+
| Result     |
|------------|
| 2018-12-20 |
+------------+

For more examples, see How to Convert a String to a Date/Time in SQL Server using CAST().

The CONVERT() Function

Here, we do the same conversion as the previous example, but with the CONVERT() function. The only difference here is the syntax.

SELECT CONVERT(date, '20 Dec 2018') AS Result;

Result:

+------------+
| Result     |
|------------|
| 2018-12-20 |
+------------+

For more examples, see How to Convert a String to a Date/Time in SQL Server using CONVERT().

The PARSE() Function

In this example, I add the weekday as part of the string. The reason I do this is to demonstrate the benefit that this function has over the others. If you provide the date in this format to the other functions, you’ll get an error. But the PARSE() function can handle this:

SELECT PARSE('Thursday, 20 Dec 2018' AS datetime2) 
AS 'Result';

Result:

+-----------------------------+
| Result                      |
|-----------------------------|
| 2018-12-20 00:00:00.0000000 |
+-----------------------------+

For more examples, see How to Convert a String to a Date/Time in SQL Server using PARSE().

The TRY_CAST() Function

You can use TRY_CAST() instead of CAST() to provide a smoother way of handling errors. When using the CAST() function, if the cast fails, it returns an error. However, if you use the TRY_CAST() function instead, it returns NULL in such cases.

Here’s an example of a cast failing:

SELECT TRY_CAST('Next month' AS date) AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

If we provide that same value to the CAST() function instead, here’s what happens:

SELECT CAST('Next month' AS date) AS Result;

Result:

Conversion failed when converting date and/or time from character string.

For more examples, see CAST vs TRY_CAST in SQL Server: What’s the Difference?

The TRY_CONVERT() Function

You can also use TRY_CONVERT() instead of CONVERT() for better error handling. If the CONVERT() function fails it returns an error. But if the TRY_CONVERT() fails, it returns NULL.

Here’s an example of a conversion failing:

SELECT TRY_CONVERT(date, 'Next month') AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

If we provide that same value to the CONVERT() function instead, here’s what happens:

SELECT CONVERT(date, 'Next month') AS Result;

Result:

Conversion failed when converting date and/or time from character string.

For more examples, see CONVERT vs TRY_CONVERT in SQL Server: What’s the Difference?

The TRY_PARSE() Function

You can also use TRY_PARSE() instead of PARSE() for better error handling. If the PARSE() function fails it returns an error. But if the TRY_PARSE() fails, it returns NULL.

Here’s an example of a conversion failing:

SELECT TRY_PARSE('Next month' AS date) AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

If we provide that same value to the PARSE() function instead, here’s what happens:

SELECT PARSE('Next month' AS date) AS Result;

Result:

Error converting string value 'Next month' into data type date using culture ''.