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:
Signs are allowed (+ or -), but you cannot combine them:
Currency symbols also result in an error:
Surprisingly, casting a decimal value to an integer also 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;
But if we change the input to a string, the implicit conversion will fail even though the SQL data looks the same.
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))
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
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:
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:
You have to use the point as the decimal separator. Even though many countries use the comma, it cannot be used in SQL Server:
You cannot even use it as a thousand separator, even though it’s a valid number:
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));
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)
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)
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 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));
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)
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');
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):
The CONVERT function finishes in half the time while doing the exact same amount of logical reads:
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
- If you want more examples on CONVERT, check out the tip Learn how to convert data with SQL CAST and SQL CONVERT and Date and Time Conversions Using SQL Server.
- More info on the FORMAT function:
- Format SQL Server Dates with FORMAT Function
- MSSQLTips.com author Aaron Bertrand dives deeper into the performance issues of FORMAT in the blog post FORMAT() is nice and all, but….
- The tip Performance Comparison of the SQL Server PARSE, CAST, CONVERT and TRY_PARSE, TRY_CAST, TRY_CONVERT Functions also takes a look at the performance of some SQL Server functions. A couple of them are also CLR based and they suffer from the same issue as FORMAT.
- Aaron has a great video tutorial on date formats: Recommended SQL Server Date Formats.
- Learn more about this function: SQL CAST Function for Data Type Conversions.
- Check out additional Function Tutorials on MSSQLTips.com:
