Data type conversion is a method used to change the data type of a value into the other type. SQL Server can convert the data type of a value in two ways: implicitly or explicitly. SQL Server performs implicit conversion for their internal needs. For example, the following query will implicitly convert the string data type into the number type in SQL Server:
SELECT 5 + '5' AS Result Here is the output:
SQL Server returns the integer value because it will convert the lower data type into the higher one when using two values with different data types. This process indicates the implicit conversion of data types.
In contrast to implicit conversions, we have explicit conversions performed by the database administrator or programmer with the help of data conversion functions. This article will mainly focus on how we can work with the CAST function in SQL Server.
The CAST function enables the user to explicitly convert a value from one data type to the other type specified in the expression. The following syntax illustrates the CAST function in SQL Server:
CAST (expression AS data_type ( length )) In the syntax, we have used the following parameters:
- expression: It specifies a literal value or valid expression that we are going to convert into another type.
- data_type: It defines the target data type in which we want to convert the expression. It can include the following data types as an input: bigint, int, smallint, tinyint, bit, Decimal, numeric, money, float, datetime, char, nchar, varchar, nvarchar, text, ntext, binary, image, etc. We make sure that the input type should not be an alias data type.
- length: It is an optional parameter for specifying the length of the target data type. By default, its value is 30.
Return Value
The CAST function will return a value in which data type we want to convert.
SQL Server CAST Function Examples
Let us understand how the CAST function works in SQL Server with the help of various examples. We can use the CAST function with the SELECT statement directly.
1. CAST Function to convert Decimal to Integer
The following example will convert the decimal number 10.95 to an integer by using the CAST() function:
SELECT CAST(10.95 AS INT) AS Result; The CAST function returns the below output:
2. CAST Function to convert Decimal into another Decimal with different length
This example will convert the decimal number into another decimal number with zero scales with the help of the CAST function:
SELECT CAST(10.95 AS DEC(3, 0)) AS Result; The CAST function returns the below output:
When we convert a data type value in different positions, SQL Server gives a truncated or rounded result according to the following rules:
| FROM DATA TYPE | TO DATA TYPE | Result |
|---|---|---|
| Numeric | Numeric | Rounded |
| Numeric | INT | Truncated |
| Numeric | Money | Rounded |
| Float | INT | Truncated |
| Float | Numeric | Rounded |
| Float | DATETIME | Rounded |
| DATETIME | INT | Rounded |
| Money | INT | Rounded |
| Money | Numeric | Rounded |
| Non-Numeric (char, nchar, varchar, or nvarchar) | INT, Float, Numeric, or Decimal | Error MessageError Message |
| Empty String | Numeric or Decimal | Error Message |
3. CAST Function to convert String to DATETIME
This example will convert the string ‘2021-04-26’ to a DATETIME value with the help of the CAST function.
SELECT CAST('2021-04-26' AS DATETIME) AS Result; The CAST function returns the below output:
4. CAST Function to convert DATETIME to Varchar
This example will convert the current date and time to a string with the help of a CAST function.
SELECT CAST(GETDATE() AS VARCHAR) AS Result; The CAST function returns the below output:
5. CAST Function with a NULL value
This example will how NULL values works with the CAST function.
DECLARE @strValue AS VARCHAR(50)
SET @strValue = NULL
SELECT CAST(@strValue AS INT) AS Result1;
SELECT CAST(NULL AS INT) AS Result2;Here is the output:
6. CAST Function with Table
Here we are going to see how to use the CAST function in tables to filter the records. Suppose we have a table named “orders” containing the following data:
In this table, we can see that the prchase_date columns contain the purchase date of items. If we want to get the item list between selected ranges of date, we can use the below statement. Here, the literal string is converted into timestamp value before evaluating the WHERE condition.
SELECT item, price, prchase_date FROM orders
WHERE prchase_date
BETWEEN CAST('2021-04-01' AS DATETIME) AND CAST('2021-04-20' AS DATETIME);This statement returns the below result:
TRY_CAST Function
It is the advanced form of the CAST function in SQL Server. It is helpful in the data conversion process to protect from getting data converting errors during the query execution. Suppose we are going to convert string data type into an integer. It’s possible that we will get the error message using the CAST function. When we will do the data analysis from various data sources, we might get such types of values in the source data. In such cases, we need to use the TRY_CAST function to handle these errors. The syntax of the CAST and TRY_CAST function have no differences. We will get the NULL result if we use the TRY_CAST function when the data conversion generates an error.
Let us see what happens if we will try to convert a string value into an integer data type by using the CAST function:
SELECT CAST('Javatpoint' AS INT) AS Result; This statement will not execute, and SQL Server raising the following error message:
However, if we use the TRY_CAST function, we will not get any error and see the NULL value in the result. See the below query:
SELECT TRY_CAST('Javatpoint' AS INT) AS Result; When we execute this query, SQL Server returns the NULL value instead of raising an error:
Leave a Reply