#BackToBasics: CAST vs. CONVERT

There are several T-SQL language features where you have a choice of two or more approaches. I’ve talked before about old-style vs. explicit inner joins, how to express the “not equal to” operator, and whether you should use the newer FORMAT function.

Another area where I see a lot of inconsistency is the simple choice between CAST and CONVERT.

In many cases, there is no material difference. For example, it is true that these do the same thing:

SELECT

  [cast]    = CAST(CURRENT_TIMESTAMP AS date),

  [convert] = CONVERT(date, CURRENT_TIMESTAMP);

Neither is really any more typing than the other, and they both work the exact same way under the covers. So it would seem that the choice between CAST and CONVERT is merely a subjective style preference. If that were always true, however, you probably wouldn’t be reading this post.

There are other cases where you need to use CONVERT in order to output correct data or perform the right comparison. Some examples:

Interpreting a date from a string

It is very common to accept freeform date strings as input, and then translate those to date or datetime within SQL Server. This is very easy to do with CONVERT – let’s say people are passing in m/d/y strings, you can get the right date by using CONVERT with style 101:

SELECT CONVERT(date, '09/13/2016', 101);

Conversely, if you know the date is being entered as d/m/y, you can simply use style 103 instead:

SELECT CONVERT(date, '13/09/2016', 103);

You could even have cases where you interpret the string input based on the user’s country or explicit preference; for example, you may have users from Canada, who will input d/m/y, and users from the USA, who will input m/d/y.

INSERT dbo.Users(UserName, Country, BirthDate)

SELECT @UserName, @Country, CONVERT(date, @BirthDateString, CASE Country

  WHEN 'Canada' THEN 103

  WHEN 'USA'    THEN 101 END);

You can’t do any of these things with CAST – it will simply assume that the string format will be in the same regional and language settings as SQL Server. If you pass in 13/09/2016 and the language is US_English, CAST will fail:

Of course, ideally, you wouldn’t be relying on any sort of string formats at all, and rather using calendar controls or drop-downs to accept date input from users. When the date entered is 13/09/2016, it’s pretty easy to try to convert it one way, and when that fails, try the other way. But when the date entered is 06/09/2016, how will you know whether the user meant June 9th or September 6th? If you control the format that is ultimately sent to SQL Server, you don’t have to guess.

Presenting a date/time with a regional string format

Similar to interpretation of date strings, you might want to conditionally display date values as specific regional strings. A few examples:

DECLARE @today datetime = CURRENT_TIMESTAMP;

SELECT CONVERT(char(10), @today, 101), -- 11/02/2016
CONVERT(char(10), @today, 103), -- 02/11/2016
CONVERT(char(10), @today, 120), -- 2016-11-02
CONVERT(char(8), @today, 112), -- 20161102
CONVERT(char(8), @today, 108); -- 09:00:04

CAST can’t really help you here; while you can simply say CAST(@today AS CHAR(10)), you can’t dictate the format. What you end up in this specific case, at least with US_English, is:

You might suggest using FORMAT, since you don’t need to memorize style numbers and it will lead to less code in some cases, but please don’t forget that this function is expensive. So while it would be fine for onesy-twosy stuff, it would be unacceptable at scale. Arguably a better solution would be to return the date/time value to the application, and let the presentation layer handle the output format – after all, that’s not really SQL Server’s job.

Swapping between varbinary and string

Converting dates to and from strings is pretty common, but a less common exercise is switching between strings and binary values. Let’s say you want to encode some string as a varbinary value:

SELECT CONVERT(varbinary(32), 'some string');

This yields:

To get the value back, you can simply use CONVERT/CAST back to a varchar:

SELECT CAST(0x736F6D6520737472696E67 AS varchar(32)),

       CONVERT(varchar(32), 0x736F6D6520737472696E67);

But what if you actually need a string representation of the 0x73... binary value? For example, to display the encoded value in a sentence or to build a comma-separated list of such values. CONVERT has your back, but once again CAST can’t help you:

SELECT CONVERT(varchar(32), 0x736F6D6520737472696E67, 1);

------- style number, pretty important -------------^^^

The output is the same as above, but now it’s a string:

I used this technique recently when I needed to construct automated e-mails for a customer, using Advisory Conditions, that included explicit plan_handle values in the message body. I didn’t want to simply CAST a plan handle to a string:

SELECT CAST(0x05000500E914345FA01B4475AA020000010 AS nvarchar(max));

Because this is the result:

Which wouldn’t be very useful, since the customer wanted to be able to copy a plan handle from the message and paste it into a query window. Using CAST, they would end up with gibberish.

Summary

CAST does not support style numbers in any way, so for many of these scenarios,CAST is simply not an option. And my point here isn’t to point out where CAST and CONVERT differ, nor to provide an exhaustive resource of all the places where style numbers can be used (the documentation does a good job of that, and I did show how to self-document all the styles available for date/time).

I’m just asking the rhetorical question, “Why would you ever choose CAST?” I always use CONVERT, even when I don’t have to, because there are so many cases where I *do* have to. Consistency does not get a lot of publicity, but it’s pretty important in my books. Just like spelling out DATEPART(DAY, ... instead of DATEPART(D, ... makes sense, even though there is no chance for ambiguity there, as there is with DATEPART(W, .... Stay consistent, and don’t take advantage of exceptions unless there is a tangible benefit to doing so (and saving three keystrokes is not a tangible benefit, IMHO).

Now, you might argue, I prefer CAST because it is ANSI standard, while CONVERT is not. Okay, valid argument, but like many other things you are probably using that are proprietary to SQL Server, you need to weigh the consistency and other benefits above against the odds that you’ll ever actually need to port the code to a different platform. I’m all for following the standard, but not when doing so gets in my way, or when it is just to appease the Celkos of the world in their glass classrooms. 🙂

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.