SQL Server CONVERT() Function

SQL Server CONVERT() Function

The SQL Server CONVERT() 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 and for formatting dates and numbers.

Syntax

SELECT CONVERT(data_type(length), expression, style);

The CONVERT() function takes three arguments:

  • data_type(length): The target data type and length. The length is optional.
  • expression: The value to be converted.
  • style: The style code for date and time conversions (optional).

Example SQL Server CONVERT() Function Queries

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

1. Converting a String to an Integer

SELECT CONVERT(INT, '123') AS int_value;

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

int_value
---------
123

2. Converting an Integer to a String

SELECT CONVERT(VARCHAR(10), 123) AS string_value;

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

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

3. Converting a Date to a String

SELECT CONVERT(VARCHAR(10), '2024-06-01', 120) AS string_date;

This query converts the date '2024-06-01' to a string in the format 'YYYY-MM-DD'. The result will be:

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

4. Converting a String to a Date

SELECT CONVERT(DATE, '2024-06-01', 120) AS date_value;

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

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

5. CONVERT() with a Column

SELECT order_id, CONVERT(DECIMAL(10, 2), order_amount) 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. CONVERT() with a Variable

DECLARE @amount VARCHAR(10) = '123.45';
SELECT CONVERT(DECIMAL(10, 2), @amount) 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 CONVERT() 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 CONVERT() Function

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

SELECT id, transaction_amount, CONVERT(DECIMAL(10, 2), transaction_amount) 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 CONVERT() function is a powerful tool for converting expressions from one data type to another and for formatting dates and numbers. Understanding how to use the CONVERT() function and its syntax is essential for effective data processing and manipulation in SQL Server.