SQL CAST() Function
FREE
Overview
In SQL before performing the calculation, when two values of different data types are used, SQL Server will attempt to convert the lower data type to the higher one. In SQL Server, this is referred to as an implicit conversion.
We also have explicit type conversion where you call the CAST() function to explicitly convert a value of one type to another. There will be instances when you have to use a function that doesn't return the value in the data type you want. This is when you use explicit type casting(or conversion). Explicit type casting functions like CAST() gives you the power to convert any data type value into your desired data type.
Syntax of CAST() in SQL
The syntax of the SQL CAST() function is as follows:
Parameters/Arguments of CAST() in SQL
The SQL CAST() function takes three parameters:
1. Expression
The given value to convert to another data type.
2. Type
The data type that you want to convert your expression to. You can convert your expression to any of the following data types:
- bigint
- int
- smallint
- tinyint
- bit
- decimal
- numeric
- money
- smallmoney
- float
- real
- datetime
- smalldatetime
- char
- varchar
- text
- nchar
- nvarchar
- ntext
- binary
- varbinary
- image
3. Length
Length is the optional data type. Here you specify the length of the data type you want to convert to.
Return Values of CAST() in SQL
The SQL CAST() function when fails to convert to the desired data type returns an error. Otherwise, it returns the converted data type value.
Example:
Output:
Output:
Remarks
A few important things that you need to keep in mind regarding the SQL CAST() function are:
- When you try to convert a float or numeric data type value to an integer, the CAST() function will truncate the result. Whereas, for every other conversion, the CAST() function will round the result.
- You can use the TRY_CAST() function if you don't want to return an error when the conversion fails. This function will return null if the conversion fails.
Applications of CAST() in SQL
The SQL CAST() function is used to convert one data type value into another data type. Hence, we can apply the CAST() function anywhere we want to perform explicit type conversion. Like in this example,
Here, the float value 24.25 is converted into the integer value 24.
Learn How to Convert Data with SQL CAST and SQL CONVERT
Both SQL CAST and SQL CONVERT are of similar nature. Both of them convert the given data type value into the desired data type. We have already seen the SQL CAST() function let's now discuss CONVERT() in brief.
Syntax
CONVERT function takes four parameters. Three of their type, expression, and length is the same as in the CAST function. The fourth parameter is an optional parameter called style. Here you can specify the style used to convert from the given data type to the desired data type.
The only difference between the SQL CAST and the SQL CONVERT is that the SQL CONVERT() is specific to SQL Server. Whereas, the CAST() function is a part of ANSI-SQL functions, which is widely available in many other database products.
More Examples
Example 1
student_table
| ID | Name | Height |
|---|---|---|
| 21 | Mudit | 175.98 |
| 22 | Geya | 155.4 |
| 23 | Pranav | 150.1 |
SQL Command
Output
Example 2
SQL Command
Output
Example 3
SQL Command
Output
Conclusion
In this article, we learned about the SQL CAST() function that's used to convert the value of a given data type to a value of desired data type. If the CAST() function fails to convert the given data type value to the desired data type, it returns an error. Otherwise, it returns the converted value in desired data type.
If we don't want to return an error when the function fails to convert the data type then we should use the TRY_CAST() function. Instead of, returning an error it returns a null value.