Converting between Data Types with the SQL Server CAST Function

By:   |   Updated: 2022-02-01   |   Comments   |   Related: More > TSQL


Free SQL Server Performance and Monitoring Report


Dear Database Professional,

Download your free copy of the MSSQLTips.com SQL Server Performance and Monitoring Report. This survey was conducted in 2022 and polled 588 database professionals about various aspects of tuning and optimizing SQL Server.

Click here to download the free report

Problem

I have a table with some imported data. All of the columns have text data types, even though the actual data is in another format. How can I convert this data to the correct data type?

Solution

In Microsoft SQL Server, there are two functions that allow you to convert data from one data type to another: CAST and CONVERT. Both functions are described in the tutorial Learn how to convert data with SQL CAST and SQL CONVERT. In this tutorial, we’ll dive a bit deeper into the CAST function which can be used in a SQL database with T-SQL scripts, SELECT statements and stored procedures.

Data Type Conversions using the SQL CAST Function

The SQL Server CAST syntax is as follows:

CAST(expression AS datatype)

For example, if we want to cast a Boolean value from the bit data type to a tiny int data type, we can do it with a valid expression such as:

DECLARE @mybit BIT = 1;

SELECT Test = CAST(@mybit AS TINYINT);

Not every data type can be converted to all possible data types. You cannot cast a date to an integer for example. In the official documentation you can find a good overview of which implicit and explicit conversions are allowed.

Casting to a numeric data type

You can cast text data to a numeric data type – such as int, bigint, numeric and so on – as long as the text actually represents a number. Let’s illustrate with some Transact-SQL examples:

WITH CTE_SampleData AS
(
    SELECT TestData = '123'
    UNION ALL
    SELECT '-456'
    UNION ALL
    SELECT '0'
    UNION ALL
    SELECT '+5123'
    UNION ALL
    SELECT NULL
)
SELECT Test = CAST(TestData AS INT)
FROM CTE_SampleData;

The result:

cast string to int

Signs are allowed (+ or -), but you cannot combine them:

cast error when using +-

Currency symbols also result in an error:

cast error when using currency symbol

Surprisingly, casting a decimal value to an integer also fails:

cast from numeric to int fails

However, implicit conversion might work, but not in all cases. If you try to implicitly convert a decimal value to an integer, it will work and the result will be a truncated value (everything after the decimal point is dropped):

DECLARE @myInt INT;
 
SET @myInt = 100.923;

SELECT @myInt;
implicit conversion from numeric to int

But if we change the input to a string, the implicit conversion will fail even though the SQL data looks the same.

implicit conversion fails from string with numeric data to int

If the source data might contain decimal values mixed with integer values, it’s safer to convert everything to the numeric or decimal data type:

SELECT CAST('100.123' AS NUMERIC(10,3))
casting string to numeric

You might be tempted to use the ISNUMERIC function to check if data can be casted, but the results are not always trustworthy. In the following script we try to use ISNUMERIC to see if we can cast a string value containing a currency symbol. ISNUMERIC will return 1, but the value still cannot be converted as demonstrated earlier:

DECLARE @myStringInput VARCHAR(10) = '€123';
 
IF ISNUMERIC(@myStringInput) = 1
BEGIN
    SELECT result = CAST(@myStringInput AS INT)
END
isnumeric fail

A better option is to use TRY_CONVERT or TRY_CAST. These functions try to cast the expression to the desired data type. If it fails, NULL is returned instead of an error.

WITH CTE_SampleData AS
(
    SELECT TestData = '123'
    UNION ALL
    SELECT '-456'
    UNION ALL
    SELECT '€123'
    UNION ALL
    SELECT '0'
    UNION ALL
    SELECT '+-5123'
)
SELECT Test = TRY_CAST(TestData AS INT)
FROM CTE_SampleData;

The result:

using try_cast to check if casting succeeds or not

When converting to decimal data types, if there are more numbers after the decimal point then the specified precision, SQL Server will round the data:

data is rounded

You have to use the point as the decimal separator. Even though many countries use the comma, it cannot be used in SQL Server:

cannot use the comma as decimal separator

You cannot even use it as a thousand separator, even though it’s a valid number:

cast sql function convert data types 012

If you have string data with a comma, you can use the REPLACE function to get rid of it. For example:

DECLARE @myInput VARCHAR(20) = '123,456.789';
SELECT CAST(REPLACE(@myInput,',','') AS NUMERIC(10,3));

In the case where a point is used as a thousand separator and a comma as the decimal separator, you’ll have to use REPLACE twice. Be sure to replace the points first, otherwise you’ll get rid of the decimal separator.

DECLARE @myInput VARCHAR(20) = '123.456,789';
SELECT CAST(REPLACE(REPLACE(@myInput,'.',''),',','.') AS NUMERIC(10,3));
converting number with both comma and point to numeric

Casting to datetime data types

It’s possible to cast numbers to the (small) datetime data type, but not to the other date data types, such as datetime2, date, datetimeoffset and so on. The idea behind converting numbers to a datetime is that the portion before the decimal point (let’s say x) adds x days to the date 1900-01-01. The portion behind the decimal point adds to the time part of the datetime. For example, the following number adds exactly 1 day and 1 second to 1900-01-01 00:00:00.

SELECT CAST(1.00001156 AS DATETIME)
cast number to datetime

Keep in mind the datetime datatype is rounded by 3 milliseconds, so it’s possible other numbers achieve the same result. Text data types can also be cast to a date data type. A variety of formats can be used:

SELECT Test = CAST('2021-12-21' AS DATETIME)
UNION ALL
SELECT CAST('01-01-2021' AS DATETIME)
UNION ALL
SELECT CAST('01-feb-2021 23:59:59' AS DATETIME)
UNION ALL
SELECT CAST('jan 12, 2021 07:12:50PM' AS DATETIME)
UNION ALL
SELECT CAST('04/11/2021 07:12AM' AS DATETIME)
different formats for dates

You can find an overview in the official documentation. Issues can arise if the format is ambiguous. For example, does ’01-02-2021’ represent the first of February (like in most parts of the world), or is it rather the second of January (as some countries do)? Unlike CONVERT, CAST doesn’t have an optional style parameter. You can influence the date style using the SET DATEFORMAT command.

SET DATEFORMAT DMY;
SELECT CAST('01-02-2021' AS DATE); -- first of Feb
 
SET DATEFORMAT MDY;
SELECT CAST('01-02-2021' AS DATE); -- second of Jan
casting different formats of dates

Casting text to a character string data type

You can cast about anything to a string data type. As such, often times you can just rely on implicit conversion. However, if you want to specify a length, the CAST function comes in handy. In the following example, a couple of expressions of different data types are cast to the varchar data type. Instead of letting SQL Server determine the length, we explicitly set one using CAST:

SELECT Test = CAST(132456 AS VARCHAR(20))
UNION ALL
SELECT CAST(GETDATE() AS VARCHAR(20))
UNION ALL
SELECT CAST('Hello MSSQLTips!' AS VARCHAR(20));
casting to varchar

When casting to a string data type, such as (N)CHAR or (N)VARCHAR, it’s a best practice to always specify a length.

As you can see in the second line of the result set of the previous example, the database engine has chosen a format for the datetime value. If you want to influence this, you’ll need to use the SET options, as explained in the previous section. When dealing with date data types, the CONVERT function is a better option than CAST.

Alternatives

The STR function is used specifically for casting numeric data to character data. It has the benefit of the option to specify a length and the number of decimals after the decimal point. The result is always varchar.

SELECT MyNumber = STR(123.456,6,2)
the STR function for casting numeric data

Important to know is that the length also includes the decimal point. Another function for casting to character data is the FORMAT function, which was introduced in SQL Server 2012. This function accepts a format option or a culture, which makes it useful for converting dates. An example:

SELECT Test = FORMAT(GETDATE(),'d','us'); 
cast sql function convert data types 019

Here we converted a date to the formatting used in the US. The problem with FORMAT is that it is CLR based which means there’s a performance overhead. Let’s demonstrate with an example on the AdventureWorks datawarehouse sample database.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
 
SELECT PerfTest = CONVERT(VARCHAR(20),[MovementDate],101)
FROM AdventureWorksDW2017.[dbo].[FactProductInventory];
 
SELECT PerfTest = FORMAT([MovementDate],'d','us')
FROM AdventureWorksDW2017.[dbo].[FactProductInventory];

One query formats a date with the CONVERT function, another with the FORMAT function.

The FORMAT function finishes on my machine in 10 seconds (for 776,256 rows):

performance format

The CONVERT function finishes in half the time while doing the exact same amount of logical reads:

CONVERT performance

Especially the difference between the CPU time is staggering: CONVERT uses only 6% of what FORMAT needs. The SQL FORMAT function can be useful, but it’s advised to use on only small data sets.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips


Article Last Updated: 2022-02-01

Comments For This Article

















get free sql tips
agree to terms