PARSE vs CAST and CONVERT

T-SQL often provides multiple ways to “skin a cat”¹ as they say. In this post, we’ll take a look at two “interesting” ways to convert dates and times from character-based columns into a column using the preferred datetime data-type.

Spring, by William McTaggart

PARSE provides a mechanism to convert a wide variety of character based dates into a datetime data type. From the Docs:

Returns the result of an expression, translated to the requested data type in SQL Server.

Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value.

You might use PARSE to convert the following string into a date:

December 21, 1989 6:00 PM

The code you might use:

Output looks like:

1989-12-21 18:00:00.000

Looks great. Ok, so now you’ve got that working, you’ll want to convert a table with a large number of rows that contain character-based dates into a datetime column. You might use:

That’ll work, but it’ll take a long time if you have a lot of rows. PARSE is sloooooow. Let’s compare the statement syntax to CAST or CONVERT. Personally, I prefer CONVERT, even though it’s, gah, 3 characters longer to type.

That’s not that much harder. Output is identical to the PARSE output from above, so I’ll not repeat it here.

So, lets compare how much time each variant takes. First, we’ll setup a couple of tables; one containing source data with dates stored in a varchar column, one for the output from PARSE and one for the output from CONVERT. The source table will contain 10,000 rows. 10,000 rows is not a lot, but it’ll suffice for this test.

The last SELECT ensures the data is in the buffer cache, which will help us get an apples-to-apples comparison of the time required to do the conversions.

Next, we’ll turn on statistics for I/O and time, then convert the data using PARSE and CONVERT:

The output looks like:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'ParseTestOutput1'. Scan count 0, logical reads 10021, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParseTest'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2000 ms,  elapsed time = 2027 ms.

(10000 rows affected)

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
Table 'ParseTestOutput1'. Scan count 0, logical reads 10021, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ParseTest'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 71 ms.

(10000 rows affected)

As you can see, the first test, using PARSE, took over 2 seconds to convert 10,000 rows. The second test, using CONVERT, took only 71 miliseconds, or 28.5 times faster.

The moral of this story? If you’re converting well-formed dates and times from character-based columns into datetime-based columns, and you care about speed, use CONVERT (or CAST, it’s the same thing). For a performance comparison of CAST vs CONVERT, see this post.

I hope you enjoyed this post, and would love it if you checked out the rest of our posts on SQL Server Performance.

^{1 – Don’t worry, no cats were harmed during the making of this post. It’s just a saying!}