SQL Server Cast Function
“Whether you are just getting started or an experienced developer, you will encounter type conversion. Type conversion refers to the process of converting a value or expression from one data type to another compatible data type.
In this post, we will discuss using the cast() function in SQL Server to convert a value or expression from one type to another.”
SQL Server Cast Function
The following snippet shows the syntax of the cast() function.
The function accepts the following parameters:
- expression – any valid expression.
- data_type – sets the target data type.
- length – an optional integer value defined as the length of the target data type (only for supported types).
The function then returns the expression converted to the target data_type.
Let us use examples to illustrate how we can use the cast function.
Using the Cast Function to Convert String to Integer
The following example uses the cast function to convert the input string to an integer value.
CAST('100' AS INT) AS output_value;
The resulting output:
------------+
100|
Using the Cast Function to Convert Decimal to Int
The second example below uses the cast function to convert a decimal type to int.
CAST(3.14159 AS INT) AS output_value;
The cast function will round off the input decimal to the nearest integer value, as shown:
------------+
3|
Using the Cast Function to Convert String to Datetime
We can also use the cast function to convert a given input string to a datetime value. An example illustration is shown below:
CAST('2022-10-10' AS datetime) AS output_value;
Resulting output:
-----------------------+
2022-10-10 00:00:00.000|
Using the Cast Function on Table Column
We can also apply the cast function to a specific column and convert the rows from that column to another data type.
For example, suppose we have a table:
We can convert the values of the size_on_disk column to integers as shown:
server_name,
CAST(size_on_disk AS INT) AS appx_size
FROM
entries;
The resulting table is as shown:
As we can see, the resulting output is expressed as integer values (rounded off).
NOTE: It is good to keep in mind that there are various conversion types.
- Implicit conversion – The SQL Server engine automatically applies the conversion operation to best match the requested operation.
- Explicit conversion – manually carried out by the user by calling conversion functions such as cast() and convert().
The following chart shows what types you can convert, the type of conversion applied, and more.
Source: Microsoft
Zeroing-In
Thank you for reading this tutorial. We trust you learned something new from this guide.
Source: linuxhint.com