When working with Microsoft’s SQL Server (and by extension, T-SQL), you have two options when making explicit conversions: CAST and CONVERT.
Many people use them frequently, and quite often interchangeably, but there are some cases when one is a better choice than the other. This post covers the similarities and the differences between the two, as well as when you’re better off using one over the other.
When doing explicit data conversions, CAST and CONVERT are available to you. Despite superficial differences in syntax, there are no material differences between the two in many cases. For example, the following two usages lead to the same result:
Option1 = CAST(date_of_birth AS date),
Option2 = CONVERT(date, date_of_birth);
In fact, Nakul Vachhrajani discovered that there’s no performance difference between the two; part of this is because, under the hood, SQL server implements CAST using CONVERT.
At a high level, CAST is an ANSI-standard function, which means that it will work when you port your T-SQL queries to a non-Microsoft database. CONVERT, however, is a function specific to T-SQL.
Any conversion that you want to make using CAST can also be done with CONVERT, though the converse is not always true. The main reason why stems from the fact that CONVERT accepts an optional style parameter that CAST does not. This parameter is used for formatting, which is helpful when you’re working with dates, times, fractional numbers, or monetary values.
For example, when you convert a DATETIME value to VARCHAR, you can specify the output format:
CONVERT(VARCHAR, date_of_birth, 101) as MMDDYYYY,
CONVERT(VARCHAR, date_of_birth, 111) as YYYYMMDD;
When You Should Choose CAST Instead of CONVERT
Because CAST is an ANSI-standard function, which means that you have one fewer discrepancy to worry about when porting queries from one database environment to another. This also means that non-SQL Server database administrators readily understand your conversion.
In cases where you want to preserve information (such as when converting between decimal/numeric values where you want to maintain the original number of decimal places), you should use CAST.
When You Should Choose CONVERT Instead of CAST
Because CAST doesn’t support output styling at all, there are times when you have to use CONVERT. If you care at all about what the result is when you perform an explicit conversion in SQL Server, you have to use CONVERT.
One typical scenario is accepting dates as strings, then converting them to an actual date format for storage in the database:
SELECT CONVERT(date, registration_date, 101);
Alternatively, you can choose the conversion output format based on something like location:
CONVERT(Date, @registrationDate, CASE Country
WHEN ‘England’ THEN 103
WHEN ‘USA’ THEN 101
Remember That You Can Use Both Together
Keep in mind, however, that you can easily use both to get the best of both worlds. As an example, suppose you wanted to convert a date to VARCHAR:
SELECT CONVERT(VARCHAR(10), date_of_birth, 102)
This results in the output formatted as yy.mm.dd.
However, suppose you wanted to cast your output explicitly as DATETIME. You’d have to use both CAST and CONVERT to do so:
SELECT CAST(CONVERT(VARCHAR(10), date_of_birth, 102) AS DATETIME
The resulting output is yy.mm.dd 00:00:00
In many cases, CAST and CONVERT can be used interchangeably though, there are certainly places where you should definitely choose one over the other. As always, the choice you make depends on your individual circumstances, but fortunately, regardless of which option you think is best, there will be no ill effects on your query’s performance.