SQL Server CAST() Function

SQL Server CAST() Function

The SQL Server CAST() function converts an expression from one data type to another. This function is useful for ensuring that data is in the correct format for processing.

Syntax

SELECT CAST(expression AS data_type);

The CAST() function takes two arguments:

  • expression: The value to be converted.
  • data_type: The target data type.

Example SQL Server CAST() Function Queries

Let's look at some examples of SQL Server CAST() function queries:

1. Casting a String to an Integer

SELECT CAST('123' AS INT) AS int_value;

This query converts the string '123' to an integer. The result will be:

int_value
---------
123

2. Casting an Integer to a String

SELECT CAST(123 AS VARCHAR(10)) AS string_value;

This query converts the integer 123 to a string. The result will be:

string_value
-------------
123

3. Casting a Date to a String

SELECT CAST('2024-06-01' AS VARCHAR(10)) AS string_date;

This query converts the date '2024-06-01' to a string. The result will be:

string_date
------------
2024-06-01

4. Casting a String to a Date

SELECT CAST('2024-06-01' AS DATE) AS date_value;

This query converts the string '2024-06-01' to a date. The result will be:

date_value
----------
2024-06-01

5. CAST() with a Column

SELECT order_id, CAST(order_amount AS DECIMAL(10, 2)) AS decimal_amount
FROM orders;

This query converts the order_amount column to a decimal data type with two decimal places. The result will show the original order_id and the corresponding decimal_amount.

6. CAST() with a Variable

DECLARE @amount VARCHAR(10) = '123.45';
SELECT CAST(@amount AS DECIMAL(10, 2)) AS decimal_amount;

This query uses a variable to store a string value and then converts it to a decimal data type with two decimal places. The result will be:

decimal_amount
--------------
123.45

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CAST() function.

Step 1: Creating a Table

This step involves creating a new table named transactions to store some sample data with transaction amounts.

CREATE TABLE transactions (
    id INT PRIMARY KEY,
    transaction_amount VARCHAR(50)
);

In this example, we create a table named transactions with columns for id and transaction_amount.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the transactions table.

INSERT INTO transactions (id, transaction_amount) VALUES (1, '100.50');
INSERT INTO transactions (id, transaction_amount) VALUES (2, '200.75');
INSERT INTO transactions (id, transaction_amount) VALUES (3, '300.25');

Here, we insert data into the transactions table.

Step 3: Using the CAST() Function

This step involves using the CAST() function to convert the transaction_amount column to a decimal data type.

SELECT id, transaction_amount, CAST(transaction_amount AS DECIMAL(10, 2)) AS decimal_amount
FROM transactions;

This query retrieves the id, transaction_amount, and the decimal value of the transaction_amount column for each row in the transactions table. The result will be:

id  transaction_amount  decimal_amount
--- ------------------- --------------
1   100.50              100.50
2   200.75              200.75
3   300.25              300.25

Conclusion

The SQL Server CAST() function is a powerful tool for converting expressions from one data type to another. Understanding how to use the CAST() function and its syntax is essential for effective data processing and manipulation in SQL Server.