In SQL Server, you can use the CONVERT() function to convert an expression of one data type to another.  Therefore, if you need to convert a string to a date/time format, this function can help.

This article contains examples to demonstrate its usage.

Syntax

The syntax goes like this:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

These arguments are defined as follows:

expression

Any valid expression.

data_type

The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

length

An optional integer that specifies the length of the target data type. The default value is 30.

style

An integer expression that specifies how the CONVERT() function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

Example 1 – Convert to date Data Type

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

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

Result:

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

Of course, the string needs to be in a style that’s able to be converted to the specified data type, otherwise you’ll get an error:

SELECT CONVERT(date, 'Homer') AS Result;

Result:

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

Example 2 – Convert to datetime Data Type

In this example we convert to the datetime data type:

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

Result:

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

You can also specify the time:

SELECT CONVERT(datetime, '2pm 20 Dec 2018') AS Result;

Result:

+-------------------------+
| Result                  |
|-------------------------|
| 2018-12-20 14:00:00.000 |
+-------------------------+

Example 3 – Convert to datetime2 Data Type

In this example we convert to the datetime2 data type:

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

Result:

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

Example 4 – Convert to datetimeoffset Data Type

In this example we convert to the datetimeoffset data type:

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

Result:

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

Example 5 – Convert to smalldatetime Data Type

In this example we convert to the smalldatetime data type:

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

Result:

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

Example 6 – Convert to time Data Type

In this example we convert to the time data type:

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

Result:

+----------+
| Result   |
|----------|
| 00:00:00 |
+----------+

In that case, we specified a date but not a time, so it came back as 00:00:00. Here’s another example where we do actually specify a time:

SELECT CONVERT(time, '2pm') AS Result;

Result:

+----------+
| Result   |
|----------|
| 14:00:00 |
+----------+

But if we add the date, we get an error (unlike an earlier example when we converted to datetime):

SELECT CONVERT(time, '2pm 20 Dec 2018') AS Result;

Result:

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

So in this case you’d need to convert to datetime or smalldatetime.

Example 7 – Omitting the Date

If you omit the date while converting to the datetime or smalldatetime data types, it will return 1900-01-01 as the date:

SELECT CONVERT(datetime, '2pm') AS Result;

Result:

+-------------------------+
| Result                  |
|-------------------------|
| 1900-01-01 14:00:00.000 |
+-------------------------+

If you get an error while trying to convert a string to a date, try the PARSE() function instead. It will work in some cases where CONVERT() doesn’t.

You might also want to check out the CAST() function, which is an ANSI SQL standard way of converting between data types.  Here’s How to Convert a String to a Date/Time using CAST().