MS SQL Server - Type Conversion

When we work with data in MS SQL Server, we often need to perform calculations or filter results based on data type. Properly converting data types ensures that our calculations are accurate and our queries return the desired results. In this article, we will discuss various type conversions in MS SQL Server.

Built-in Data Types in MS SQL Server

MS SQL Server has various built-in data types to store different types of data. These are the common built-in data types in MS SQL Server −

• int: Used to store integers.

• decimal: Used to store decimal numbers.

• varchar: Used to store variable-length strings.

• dateTime: Used to store date and time values.

• bit: Used to store boolean values.

Example

Consider a table called "Products". It contains information about products, name, price, and quantity in stock. We can define the "price" column as a decimal data type, while the "quantity_in_stock" column can be defined as an int data type.

Implicit Data Type Conversion

MS SQL Server automatically converts one data type to another if necessary when performing operations on different data types. This is known as implicit data type conversion.

Example

Consider a table called "sales". It contains information about sales, sale price and the quantity sold. Write a query to calculate the total revenue from sales like this −

SELECT sale_price * quantity_sold AS total_revenue FROM sales

In this query, MS SQL Server will automatically convert the quantity_sold value from an int data type to a decimal data type. Then it performs the multiplication. Since we cannot multiply an int and a decimal without first converting one of the values.

Explicit Data Type Conversion

We can also explicitly convert data types using the CAST and CONVERT functions.

• CAST function converts an expression of one data type to another data type. The syntax for the CAST function is:

CAST ( expression AS data_type [ ( length ) ] )

• CONVERT function converts an expression of one data type to another data type with a specific format style. The syntax for the CONVERT function is:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example

Consider a table called "orders". It contains information about orders, including the order date. Write a query to retrieve the order date as a string in the format "MM/DD/YYYY" like this −

SELECT CONVERT(varchar, order_date, 101) AS 'Order Date' FROM orders

In this query, we used the CONVERT function to convert the order_date value to a varchar data type with format style 101. It represents the format "MM/DD/YYYY".

Converting Data Types in Queries

We can also use data type conversion in queries to convert a data type to a different data type. This is especially useful when filtering query results.

Example

Consider the "products" table from the earlier example. You want to filter the results to show only products with a price less than 10. Query will be like this

SELECT *
FROM products
WHERE CAST(price AS int) < 10

In this query, we used the CAST function to convert the price value to an int data type. It allows us to compare it with the integer value 10.

Handling Conversion Errors

Sometimes, conversion errors can occur when converting data types. For example, if we try to convert a string value to an integer and the string is not a valid integer value. So MS SQL Server will throw a conversion error. To handle these errors, we can use the TRY_CONVERT function. This attempts to convert a value to a specified data type. Then it returns NULL if the conversion fails.

Example

Consider a table called "employees". It contains information about employees, employee ID and hire date. You want to filter the results to show only employees hired before a certain date. But the hire date is stored as a varchar data type. We could write a query like this −

SELECT *
FROM employees
WHERE TRY_CONVERT(date, hire_date) < '01/01/2022'

In this query, we used the TRY_CONVERT function. It attempts to convert the hire_date value to a date data type. If the conversion fails, the function returns NULL. It prevents the query from throwing a conversion error.

Conclusion

MS SQL Server has built-in data types and functions for performing type conversion. You should know properly converting data types is essential for writing accurate and effective queries. By using the techniques discussed in this article. You can ensure that your queries return desired results, even when working with data of different types.