PARSE() vs CAST() vs CONVERT() in SQL Server: What’s the Difference?

Posted on by Ian

Perhaps you’ve encountered the T-SQL PARSE(), CAST(), and CONVERT() functions when working with SQL Server and wondered what the difference is. All three functions seem to do the same thing, but there are subtle differences between them.

In this article I aim to outline the main differences between these functions.

Comparison

Here’s a table that outlines the main differences between the CONVERT(), CAST(), and PARSE() functions in SQL Server:

CONVERT() CAST() PARSE()
Official Definition Converts an expression of one data type to another. Converts an expression of one data type to another. Returns the result of an expression, translated to the requested data type in SQL Server.
Accepted Value Any valid expression. Any valid expression. String.
Return Value 2nd argument, translated to the requested data type as provided by the 1st argument. 1st argument, translated to the requested data type as provided by the 2nd argument. 1st argument, translated to the requested data type as provided by the 2nd argument.
Supported Conversions Between any two data types. Between any two data types. From string to date/time and number types only.
Accepts the style Argument? Yes. No. No.
Accepts the culture Argument? No. No. Yes.
Requires .NET Framework? No. No. Yes.

Some other points in addition to the above table:

  • The Microsoft documentation points out that PARSE() will not be remoted (since it depends on the presence of the CLR). Remoting a function that requires the CLR would cause an error on the remote server.
  • There are some values that PARSE() can deal with but CAST() and CONVERT() can’t (for example, strings using certain date formats).
  • CAST() is included in the ANSI SQL-92 standard.
  • Some argue that CAST() has better performance than the other two.
  • There’s a certain performance overhead when parsing string values. Therefore, PARSE() will typically run slower than the other two.

Below are examples of situations where each function would be the most suitable.

When to use CAST()

A good argument could be made for using CAST() for any scenario that’s not listed below. As mentioned, CAST() has been part of the ANSI SQL standard since SQL-92, so it should be more portable between different DBMSs (if that’s a requirement).

Also, some argue that CAST() has better performance than the other two (here’s an interesting article that compares the performance between all three functions).

However, there are also valid reasons you might prefer (or need) to use CONVERT() over CAST().

When to use CONVERT()

The CONVERT() function can come in handy when you need to use the style argument to specify how the date should be formatted when converting between a date and a string. Here are some examples:

DECLARE @date datetime2 = '2018-06-07 02:35:52.8537677';
SELECT
    CONVERT(nvarchar(30), @date, 100) AS '100',
    CONVERT(nvarchar(30), @date, 101) AS '101',
    CONVERT(nvarchar(30), @date, 102) AS '102',
    CONVERT(nvarchar(30), @date, 103) AS '103';

Result:

+---------------------+------------+------------+------------+
| 100                 | 101        | 102        | 103        |
|---------------------+------------+------------+------------|
| Jun  7 2018  2:35AM | 06/07/2018 | 2018.06.07 | 07/06/2018 |
+---------------------+------------+------------+------------+

You can only do this with CONVERT() because:

  • CAST() doesn’t support the style argument; and
  • PARSE() doesn’t convert from a date/time to a string value (it also doesn’t support the style argument)

When to use PARSE()

Despite the various disadvantages of this function (performance, dependency on .NET, limited data type conversions), it also has some advantages, and there are some scenarios where it could be your only choice. For example, when providing a date that includes the weekday name, like Friday, 20 July 2018.

When the Others Return an Error

Here’s are examples where PARSE() is the only function of the three that can successfully convert the value without throwing an error.

In these examples, we attempt to convert various string values to a date data type. However, the string values we provide include the weekday name. This causes issues for CAST() and CONVERT(), but PARSE() has no problem.

PARSE()

SELECT 
    PARSE('Friday, 20 July 2018' AS date) AS 'Result 1',
    PARSE('Fri, 20 July 2018' AS date) AS 'Result 2',
    PARSE('Friday 20 July 2018' AS date) AS 'Result 3';

Result:

+------------+------------+------------+
| Result 1   | Result 2   | Result 3   |
|------------+------------+------------|
| 2018-07-20 | 2018-07-20 | 2018-07-20 |
+------------+------------+------------+

So PARSE() has no problem with the format of the date that we provide.

CONVERT()

SELECT 
    CONVERT(date, 'Friday, 20 July 2018') AS 'Result 1',
    CONVERT(date, 'Fri, 20 July 2018') AS 'Result 2',
    CONVERT(date, 'Friday 20 July 2018') AS 'Result 3';

Result:

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

So CONVERT() is unable to convert the string when it’s in such a format.

CAST()

SELECT 
    CAST('Friday, 20 July 2018' AS date) AS 'Result 1',
    CAST('Fri, 20 July 2018' AS date)AS 'Result 2',
    CAST('Friday 20 July 2018' AS date) AS 'Result 3';

Result:

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

And CAST() returns the same error.

So if you find yourself getting errors with the other two functions, try PARSE() instead.

Specifying the Culture

Another scenario where you might prefer to use the PARSE() function is when specifying the culture/language that the string is provided in. PARSE() has an optional argument that allows you to specify which culture to use. If omitted, the language of the current session is used.

Example of including the culture argument:

SELECT 
    PARSE('07/01/2018' AS date USING 'en-US') AS 'Result 1',
    PARSE('07/01/2018' AS date USING 'de-DE') AS 'Result 2';

Result:

+------------+------------+
| Result 1   | Result 2   |
|------------+------------|
| 2018-07-01 | 2018-01-07 |
+------------+------------+

A Culture Alternative

Despite the benefit of being able to specify the culture with PARSE(), you do have the ability to change the language settings, which means you could achieve the same effect when using CAST() or CONVERT().

For example, using SET LANGUAGE us_english prior to the query will change the current language settings to us_english. While this doesn’t allow you to specify different cultures within the query (like in the above example), it does affect the whole query (and any subsequent queries).

You can also change the date format settings in the same way. For example, SET DATEFORMAT mdy.

Here’s an example of changing the language setting prior to running a query with CAST() and CONVERT():

German:

SET LANGUAGE German;
SELECT CONVERT(date, '07/01/2018') AS 'Convert';
SELECT CAST('07/01/2018' AS date) AS 'Cast';

Result:

+------------+
| Convert    |
|------------|
| 2018-01-07 |
+------------+
Die Spracheneinstellung wurde in Deutsch geändert.
+------------+
| Cast       |
|------------|
| 2018-01-07 |
+------------+

us_english:

SET LANGUAGE us_english;
SELECT CONVERT(date, '07/01/2018') AS 'Convert';
SELECT CAST('07/01/2018' AS date) AS 'Cast';

Result:

+------------+
| Convert    |
|------------|
| 2018-07-01 |
+------------+
Changed language setting to us_english.
+------------+
| Cast       |
|------------|
| 2018-07-01 |
+------------+

Remember, when you do this, you’re changing the language/date format environment for the session. Don’t forget to change it back!