Learn CAST and CONVERT, the two SQL Server functions for explicit data-type conversion. CAST uses simple syntax for converting between types like decimal to int or date to string. CONVERT adds optional style codes for formatting, like style 104 for dd.mm.yyyy dates, and runs slightly faster since SQL Server converts CAST calls to CONVERT internally.
Chapters
Now that you have mastered implicit conversion, it's time to remember that in SQL Server there are two possibilities to convert 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 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 a 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 cost, 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.
