#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
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
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:
It is very common to accept freeform date strings as input, and then translate those to
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
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
CAST will fail:
Conversion failed when converting date and/or time from character string.
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.
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
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.
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
SELECT CONVERT(varbinary(32), 'some string');
To get the value back, you can simply use
CAST back to a
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.
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
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. :-)