SQL Server Tutorial: Explicit conversion

Want to learn more? Take the full course at https://learn.datacamp.com/courses/functions-for-manipulating-data-in-sql-server at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---

Now that you have mastered implicit conversion, it's time to remember that, in SQL Server there are two possibilities to convert data: implicit and explicit. In this video, we will focus on the second type, the explicit conversion.

Let's do a short recap of implicit conversion in order to understand why it is different than the explicit one.

Implicit conversions are not controlled by the user. They are performed automatically by SQL Server, when values having different data types are compared one to another. The conversion is made taking into account data type precedence. Also, the two data types should be compatible for implicit conversion.

On the other hand, explicit conversion is done by the user, in the query, using one of the two built-in functions: CAST() and CONVERT(). Let's take a closer look at them.

The syntax for CAST() is pretty simple: you provide as a parameter for this function the expression you want to transform and the desired data type. If the new type allows it, you can also specify a length for your data.

Take a look at these examples of how you can use the CAST() function. You see here how a decimal can be transformed into an int, a string to a decimal, a date to a string and a date to a float.

The CONVERT() function is similar to CAST(), but it accepts more parameters, so the syntax is a bit different.

The first parameter is the data type, to which you can specify the length and the second is the expression to be converted.

You can also provide an optional parameter for style, used mostly when manipulating dates. This parameter specifies how the result of the CONVERT() function will be formatted.

Take a look at these examples, to check how you can use CONVERT() in a query.

In the first conversion, a decimal is transformed into an int, then a string to a decimal (here, we specify the scale and the precision).

In the third example, the current date is converted to a character string. The "style" parameter is used to format the result. The number 104 is a code for the following date format: the day, followed by a dot, the month and the year at the end.

You can find in the MSDN documentation a list with all formatting styles you can use.

Besides the difference in syntax, the functions for explicit conversion are very similar. At this point, you may wonder why there are two functions that do the same thing. The answer is simple:

CAST() is a function from the SQL standard, while CONVERT() is SQL Server specific.

If you're writing your queries with the intention of migrating them in the future to a different platform than SQL Server, you should use CAST() for explicit conversion.

In terms of performance, CONVERT() is slightly better. This is because when using CAST(), SQL Server first transforms it into CONVERT() so it executes an additional operation.

Let's do some explicit conversions now, until it becomes natural to you to work with these functions.

#DataCamp #SQLServerTutorial #FunctionsforManipulatingDatainSQLServer #ManipulatingDatainSQLServer