What Are The SQL Server CAST Function and CONVERT Functions In SQL Server
CAST Is A Function
In T-SQL, CAST is a function that is used to convert a data type of an expression to another data type. It is commonly used when you need to change the data type of a column or a variable to a different data type, such as converting a string to a numeric data type or vice versa.
The syntax of the CAST function in T-SQL is as follows:
CAST ( expression AS data_type [ ( length ) ] )Here, expression is the value or expression that you want to convert, data_type is the target data type to which you want to convert the expression, and length (optional) is the length of the target data type.
For example, to convert a string value '123' to an integer data type, you can use the following query:
SELECT CAST('123' AS INT);This will return an integer value of 123.
CONVERT Function
In SQL Server, the CONVERT function is used to convert a data type to another data type. It's similar to the CAST function, but provides more flexibility and control over the conversion process. The CONVERT function allows you to specify the source data type, the target data type, and the style of the target data type.
The syntax of the CONVERT function is:
CONVERT(target_data_type, expression [, style])target_data_type: The data type to which you want to convert the expression.
expression: The expression to be converted.style: (optional) The style of the target data type. This parameter is only used for data types that have a style parameter.
CAST Function Or CONVERT Function For A SQL Query?
In T-SQL, both CAST and CONVERT functions are used to convert data types from one type to another. However, there are some differences between the two functions:
Syntax: The syntax for CAST and CONVERT functions are slightly different. CAST function uses the following syntax:
CAST ( expression AS data_type [ ( length ) ] )whereas CONVERT function uses the following syntax:
CONVERT ( data_type [ ( length ) ], expression [ , style ] )Style parameter: CONVERT function provides an optional third parameter, the style parameter, which is used to specify the format of the output. For example, if you are converting a date to a string, you can specify the style parameter to format the date in a specific way. CAST function does not provide any style parameter.
Flexibility: CONVERT function is more flexible than CAST function, as it supports many more data types and styles. For example, CONVERT function supports converting datetime to smalldatetime or nvarchar to datetimeoffset, which are not supported by the CAST function.
Error handling: When casting fails due to a type conversion error, CAST function will throw an error and stop the query execution, whereas CONVERT function can be set to handle errors in a more graceful way, such as returning a null value instead of throwing an error.
In general, if you need a simple data type conversion and don't need to specify any special formatting, CAST function is the preferred option. If you need more flexibility or want to specify a particular format for the output, CONVERT function is a better choice.
Convert to Integer Value Using CONVERT or CAST
Here are examples of converting a string value to an integer value using both the CONVERT and CAST functions in T-SQL:
Using CONVERT:
SELECT CONVERT(INT, '123');In the following example this will return an integer value of 123.
Using CAST:
SELECT CAST('123' AS INT);This will also return an integer value of 123.
Both of these queries achieve the same result, which is to convert the string value '123' to an integer data type. The choice between the two functions depends on personal preference and the specific requirements of the query. In general, CAST is a bit more concise and easier to read, while CONVERT is more flexible and can be used for more advanced conversions.
SQL CAST NUMBER To Character Value
In SQL, you can use the CAST or CONVERT function to convert a number to a string. Here is an example using the CAST function:
SELECT CAST(123 AS VARCHAR(10));This will convert the number 123 to a string and return the result as a VARCHAR data type. The result will be the string '123'.
Here Is An Example Using The CONVERT Function:
SELECT CONVERT(VARCHAR(10), 123);This will also convert the number 123 to a string and return the result as a VARCHAR data type. The result will be the string '123'.
Both of these queries achieve the same result, which is to convert the number to a string. The choice between the two functions depends on personal preference and the specific requirements of the query. In general, CAST is a bit more concise and easier to read, while CONVERT is more flexible and can be used for more advanced conversions.
Binary Data Type
In SQL, you can use the CAST or CONVERT function to convert binary values to other data types. Here is an example using the CAST function to convert a binary value to a string:
SELECT CAST(0x48656c6c6f20 AS VARCHAR(20));
SELECT CAST(0x48656c6c6f20 AS VARCHAR(20));This will convert the binary value 0x48656c6c6f20 (which represents the ASCII string "Hello ") to a string and return the result as a VARCHAR data type. The result will be the string "Hello ".
The CONVERT function to convert a binary value to Integer Value
This will convert the binary value 0x1234 to an integer data type and return the result as an INT data type. The result will be the integer value 4660.
Both CAST and CONVERT functions can be used to convert binary values to other data types. The choice between the two functions depends on personal preference and the specific requirements of the query. CAST function is a bit more concise and easier to read, while CONVERT function is more flexible and can be used for more advanced conversions.
Cast To A Character String
Here's an example of how to cast a value to a character string in T-SQL:
DECLARE @x INT
SET @x = 123
DECLARE @y VARCHAR(50)
SET @y = CAST(@x AS VARCHAR(50))
SELECT @y
Date Format and time styles
In SQL, you can use the CAST or CONVERT function to format and convert date and time values. Here are some examples of using CAST and CONVERT functions with different date and time styles:
Using SQL CAST Function:
SELECT CAST('2022-02-24 15:30:45' AS DATE);This will convert the string '2022-02-24 15:30:45' to a DATE data type and return the result as a date value. The result will be '2022-02-24'.
SELECT CAST('2022-02-24 15:30:45' AS TIME);This will convert the string '2022-02-24 15:30:45' to a TIME data type and return the result as a time value. The result will be '15:30:45.0000000'.
Using CONVERT:
This will convert the current date and time value returned by the GETDATE function to a string value in the format 'yyyy-mm-dd hh:mi:ss'. The result will be a string value representing the current date and time in the specified format.
SELECT CONVERT(DATETIME, '2022-02-24T15:30:45', 126);
This will convert the string '2022-02-24T15:30:45' to a DATETIME data type and return the result as a datetime value. The result will be '2022-02-24 15:30:45.000'.
In these examples, the date and time styles are specified using different codes. For example, style code 120 is used to specify the format 'yyyy-mm-dd hh:mi:ss', while style code 126 is used to specify the ISO 8601 format for dates and times. The choice of style code depends on the specific formatting requirements of the query.
CAST Date Data Type to TIMESTAMP
In SQL, you can use the CAST or CONVERT function to convert a DATE value to a TIMESTAMP value. Here's an example using the CAST function:
SELECT CAST('2022-02-24' AS TIMESTAMP);This will convert the date value '2022-02-24' to a TIMESTAMP value, where the time part will be set to 00:00:00.000000. The result will be a TIMESTAMP value representing the start of the day on February 24, 2022.
Here's an example using the CONVERT function:
SELECT CONVERT(TIMESTAMP, '2022-02-24');This will also convert the date value '2022-02-24' to a TIMESTAMP value, where the time part will be set to 00:00:00.000000. The result will be a TIMESTAMP value representing the start of the day on February 24, 2022.
Both of these queries achieve the same result, which is to convert a DATE value to a TIMESTAMP value. The choice between the two functions depends on personal preference and the specific requirements of the query. In general, CAST is a bit more concise and easier to read, while CONVERT is more flexible and can be used for more advanced conversions.
Truncating and Rounded Value
In SQL, you can use the CAST or CONVERT function to truncate or round results based on your requirements. Here are some examples of using CAST and CONVERT functions to truncate or round results using the CASE expression:
Truncating results:
SELECT CASE
WHEN CAST(123.456 AS DECIMAL(10,2)) > 100
THEN CAST(123.456 AS DECIMAL(10,2))
ELSE CAST(123.456 AS DECIMAL(10,0))
END AS [Truncated Result];
The above example will truncate the decimal value 123.456 to 123.00, as the result is less than or equal to 100. The result will be 123.00.
Rounding results:
SELECT CASE
WHEN CAST(123.456 AS DECIMAL(10,2)) > 100
THEN ROUND(CAST(123.456 AS DECIMAL(10,2)), 1)
ELSE ROUND(CAST(123.456 AS DECIMAL(10,0)), -1)
END AS RoundedResult;The above example will truncate the decimal value 123.456 to 123.00, as the result is less than or equal to 100. The result will be 123.00.
Rounding results:
SELECT CASE
WHEN CAST(123.456 AS DECIMAL(10,2)) > 100
THEN ROUND(CAST(123.456 AS DECIMAL(10,2)), 1)
ELSE ROUND(CAST(123.456 AS DECIMAL(10,0)), -1)
END AS RoundedResult;This will round the decimal value 123.456 to 123.5, as the result is greater than 100. The result will be 123.5.
In these examples, the CAST function is used to convert the decimal value to a decimal data type with a specific precision and scale. The CASE expression is then used to check if the result needs to be truncated or rounded based on some condition, and the appropriate function (CAST, ROUND) is used to perform the desired operation. The choice between truncating or rounding the result depends on the specific requirements of the query.
Certain Datetime Conversions Are Nondeterministic
Nondeterministic values are values that can vary each time they are evaluated, even if the input values and functions are the same. In SQL Server, some functions, including the CAST and CONVERT functions, can generate nondeterministic values under certain circumstances.
The CAST and CONVERT functions can generate nondeterministic values when converting date and time values using styles that include milliseconds, such as styles 113 and 114. This is because the exact value of the milliseconds component can vary depending on the system clock and the time the function is executed. For example:
SELECT CAST('2023-02-24 12:34:56.789' AS DATETIME2(3)) AS Result1,
CONVERT(DATETIME2(3), '2023-02-24 12:34:56.789', 113) AS Result2;In this example, we are converting a string to a DATETIME2(3) data type using the CAST function, and using the CONVERT function with style 113 to convert the same string to a DATETIME2(3) data type.
Both functions are specifying a precision of 3 decimal places for milliseconds. However, because the exact value of the milliseconds component can vary depending on the system clock, the results may differ each time the functions are executed.
To avoid generating nondeterministic values when using the CAST and CONVERT functions, it is recommended to avoid using styles that include milliseconds or to specify a fixed value for the milliseconds component. For example, you could use style 120 to convert date and time values to the ISO8601 format, which does not include milliseconds:
SELECT CONVERT(DATETIME2(0), '2023-02-24T12:34:56', 120) AS Result;In above example, we are converting a string to a DATETIME2(0) data type using the CONVERT function with style 120, which specifies the ISO8601 format without milliseconds. Because the format does not include milliseconds, the result is deterministic and will be the same each time the function is executed.
Use CAST with arithmetic operators
You can use the CAST function with arithmetic operators in a SQL query to perform mathematical operations on data that is stored as a different data type. Here's an example of how to use the CAST function with arithmetic operators:
SELECT CAST('5' AS INT) + CAST('3.5' AS FLOAT) AS Result;In this example, we are adding an integer value and a floating-point value together. However, the values are stored as strings, so we need to use the CAST function to convert them to their appropriate data types. We use CAST('5' AS INT) to convert the string '5' to an integer data type, and CAST('3.5' AS FLOAT) to convert the string '3.5' to a floating-point data type. Then we use the '+' operator to add the two values together.
The result of this query will be:
Result
------
8.5Note that when you perform arithmetic operations on values of different data types, SQL Server will automatically convert the values to a data type with a higher precedence. In this example, the integer value is converted to a floating-point data type before the addition operation is performed.
It's important to use the appropriate data type when performing arithmetic operations to avoid data loss or unexpected results. The CAST function can be used to ensure that data is in the correct format before performing arithmetic operations.
Effects of data type precedence in allowed conversions
Data type precedence is the order in which SQL Server evaluates data types when they are used together in expressions. Data types with higher precedence are implicitly converted to data types with lower precedence when used in an expression with a data type of lower precedence.
When using the CAST and CONVERT functions, the target data type must have lower precedence than the source data type. If the target data type has higher precedence than the source data type, the CAST or CONVERT function will fail.
Here are some effects of data type precedence in allowed conversions with CAST and
CONVERT:
Precision loss:
When converting data from a data type with higher precision to a data type with lower precision, the converted value may lose precision. For example, converting a decimal value with a large number of decimal places to an integer data type will result in a loss of decimal places.
Overflow/underflow:
When converting data from a data type with a larger range to a data type with a smaller range, the converted value may overflow or underflow. For example, converting a large integer value to a small integer data type may result in an overflow error.
Implicit conversions:
When using expressions that combine different data types, SQL Server will implicitly convert data types with lower precedence to data types with higher precedence. This can result in unexpected results if the data types are not compatible. For example, adding a string and a numeric value may result in a concatenation of the two values, rather than a mathematical operation.
To avoid these issues, it's important to use the appropriate data types for your data and to be aware of the data type precedence when using expressions and functions in your SQL queries. If you need to convert data types, use the CAST or CONVERT functions and ensure that the target data type has lower precedence than the source data type.
Tips and Tricks
Here are some tips and tricks for using the CAST and CONVERT functions in SQL Server:
Choose the right data type:
When using CAST or CONVERT, it's important to choose the correct data type for the output. Using an incorrect data type can lead to data loss, truncation, or unexpected results.
Understand data type precedence:
When using CAST or CONVERT, make sure that the target data type has lower precedence than the source data type. Otherwise, the conversion may fail.
Use style codes for date and time conversions:
When converting date and time values, use style codes to specify the format of the output. This can help ensure that the output is in the correct format and is consistent across different systems.
Be aware of data type limitations:
Some data types, such as VARCHAR(MAX) and NVARCHAR(MAX), have limitations on the maximum size of data they can hold. When converting data to these types, be aware of these limitations and ensure that the data is not truncated.
Use TRY_CAST and TRY_CONVERT for error handling:
When using CAST or CONVERT, errors can occur if the input data is not compatible with the target data type. To handle these errors gracefully, use the TRY_CAST and TRY_CONVERT functions, which return NULL instead of throwing an error if the conversion fails.
Use CAST instead of CONVERT for simple conversions:
If you only need to perform a simple conversion, such as converting an integer to a string, use the CAST function instead of CONVERT. CAST is faster and easier to read than CONVERT for simple conversions.
Test your queries:
Before using CAST or CONVERT in a production environment, test your queries to ensure that they are returning the expected results and that they are performing efficiently.
By following these tips and tricks, you can use CAST and CONVERT effectively in your SQL Server queries to convert data types, format date and time values, and handle errors.
Last Notes:
If you need help with picking between implicit conversion vs explicit conversions and need assistance with your transact sql either in SQL Server or Azure SQL Database lets connect!
I have 20 years of experience differentiating between numeric Money, type float and decimal number to create the perfect SQL CAST Function. Contact me below for a 30 minute consultation.
Video
BPS Related Content
External Links