Convert String to Datetime in SQL Server

Summary: in this tutorial, you will learn how to convert a string to a datetime in SQL Server using the CONVERT() and TRY_CONVERT() function.

Introduction to CONVERT() and TRY_CONVERT() functions

SQL Server provides the CONVERT() function that converts a value of one type to another:

CONVERT(target_type, expression [, style])
Code language: SQL (Structured Query Language) (sql)

Besides the CONVERT() function, you can also use the TRY_CONVERT() function:

TRY_CONVERT(target_type, expression [, style])
Code language: SQL (Structured Query Language) (sql)

The main difference between CONVERT() and TRY_CONVERT() is that in case of conversion fails, the CONVERT() function raises an error while the TRY_CONVERT() function returns NULL.

This example uses the CONVERT() function to convert a string in ANSI date format to a datetime:

  SELECT 
    CONVERT(DATETIME, '2019-08-15', 102) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-----------------------
2019-08-15 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

If the conversion fails, the CONVERT() function will raise an error:

SELECT 
    CONVERT(DATETIME, '2019-18-15', 102) result;
Code language: SQL (Structured Query Language) (sql)

The following is the error message:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Code language: SQL (Structured Query Language) (sql)

The TRY_CONVERT() function, on the other hand, returns NULL instead of raising an error if the conversion fails:

SELECT 
    TRY_CONVERT(DATETIME, '2019-18-15', 102) result;
Code language: SQL (Structured Query Language) (sql)

The output is:

result
-----------------------
NULL

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Converting a string in ANSI/ISO and US date format to a datetime

Both CONVERT() and TRY_CONVERT() function can recognize ANSI/ISO and US formats with various delimiters by default so you don’t have to add the style parameter.

This example shows how to use the CONVERT() function to convert strings in ISO date format to datetime values:

SELECT CONVERT(DATETIME, '2019-09-25');
SELECT CONVERT(DATETIME, '2019/09/25');
SELECT CONVERT(DATETIME, '2019.09.25');
SELECT CONVERT(DATETIME, '2019-09-25 12:11');
SELECT CONVERT(DATETIME, '2019-09-25 12:11:09');
SELECT CONVERT(DATETIME, '2019-09-25 12:11:09.555');
SELECT CONVERT(DATETIME, '2019/09/25 12:11:09.555');
SELECT CONVERT(DATETIME, '2019.09.25 12:11:09.555');
Code language: SQL (Structured Query Language) (sql)

Note that the CONVERT() function can also convert an ISO date string without delimiters to a date value as shown in the following example:

SELECT 
    CONVERT(DATETIME, '20190731') result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-----------------------
2019-07-31 00:00:00.000

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

The CONVERT() and TRY_CONVERT() functions can convert United States datetime format (month, day, year and time) by default, therefore, you don’t need to specify style 101:

SELECT TRY_CONVERT( DATETIME, '12-31-2019');
SELECT TRY_CONVERT( DATETIME, '12/31/2019');
SELECT TRY_CONVERT( DATETIME, '12.31.2019');
SELECT TRY_CONVERT( DATETIME, '12-31-2019 12:15');
SELECT TRY_CONVERT( DATETIME, '12/31/2019 12:15:10');
SELECT TRY_CONVERT( DATETIME, '12.31.2019 12:15:10.333');
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to convert a string to a datetime using the CONVERT() and TRY_CONVERT() functions.

Was this tutorial helpful?