By: Koen Verbeeck | Updated: 2021-09-16 | Comments (1) | Related: 1 | 2 | 3 | > Functions System
Problem
When working with data, conversion from one data type to another is a common use case. For example, you imported data from Excel and all data is stored as text. However, some columns are of the numeric or datetime format and you want to convert the data to their corresponding data types. In this tutorial, we'll show you how you can convert between different data types in Microsoft SQL Server.
Solution
The T-SQL language offers two functions to convert data from one data type to a target data type: CAST and CONVERT. In many ways, they both do the exact same thing in a SELECT statement or stored procedure, but the SQL Server CONVERT function has an extra parameter to express style.
The syntax is as follows:
CAST(expression AS datatype(length)) CONVERT(datatype(length), expression, style)
Let's illustrate with an example. Suppose we have the character string '123' stored as text, but we want to convert it to an integer. Using the two functions, we get the following Transact-SQL statements:
SELECT CAST('123' AS INT);
SELECT CONVERT(INT,'123');
Both return the exact same output:
With CONVERT, we can do a bit more than with SQL Server CAST. Let's say we want to convert a date to a string in the format of YYYY-MM-DD. We can do this with the following expression:
SELECT CONVERT(VARCHAR(30),GETDATE(),23);
The number 23 is the style and it tells SQL Server how we want the string to be formatted. For example, if we use 110 we get a completely different formatting:
If you want to use custom formatting strings instead of these pre-defined styles, check out the FORMAT function. You can use FORMAT for converting dates and numerical values to strings, but for other data type conversions you need to stick with CAST or CONVERT. You can learn more about the FORMAT function in the tip Format SQL Server Dates with FORMAT Function.
If you want to learn more about the CONVERT function and different date and time formats check out this article Date and Time Conversions Using SQL Server.
Which to use CAST function or CONVERT function for a SQL query?
When you don't need a style, CAST and CONVERT are the same. So when would you use one or the other? It boils down to personal preference, but it's good to know CAST is ANSI SQL (meaning it's part of the overall SQL standard) while CONVERT isn't. Migrating SQL code between different database systems is a bit easier if you stick with ANSI standard SQL.
Convert to String using CONVERT or CAST
About any data type can be converted to a string. There are a couple of notable exceptions:
- The image data type cannot be converted to string. It can only be converted to binary or varbinary. This data type is deprecated.
- The timestamp data type cannot be converted to Unicode strings. Despite it's name, it has nothing to do with dates or time, but is actually some sort of row version. It's also deprecated.
When converting to string, you need to specify a length, but this is not mandatory. For example, when we convert the following string to varchar data type, we do the following:
SELECT CONVERT(VARCHAR(250),'This is a really long piece of string') AS TestString
However, if we don't specify the length, we see that a piece of the string is truncated:
Where did the last piece go? When you don't specify a length, SQL Server will assume a default length of 30 which is shorter than our string. However, this is only when we do this in CAST or CONVERT. When you assign a variable for example, SQL Server uses a default of 1.
DECLARE @myStringVariable VARCHAR; SET @myStringVariable = 'This is a really long piece of string'; SELECT @myStringVariable;
A best practice is to always specify a length when declaring data types. When converting a date, you also might run into issues when you don't specify a style. Let's take the following example:
SELECT CONVERT(VARCHAR(10),GETDATE());
Because the style was left out and SQL Server had to choose a default style, the result is truncated:
This can be fixed by choosing an appropriate style (or by making the string larger of course):
When converting numerical data to string data types, you'll get "unexpected results" if the data is too long to fit in the string. For example:
SELECT CONVERT(CHAR(1),132456);
In the case of Unicode data types an error is returned instead:
SELECT CONVERT(NCHAR(1),132456);
Convert to Integer Value using CONVERT or CAST
It's common when importing data from Excel or flat files, that all data is imported as text. In this case, you'll want to convert actual numbers to the correct data type. For example:
SELECT CONVERT(INT,'123');
Converting a decimal number will result in an error if integer is the destination data type:
SELECT CONVERT(INT,'123.123');
However, it's no problem if the source data type is numeric (or decimal):
SELECT CONVERT(INT,123.923);
In this case, the numeric is truncated after the decimal point.
Convert to Numeric using CONVERT or CAST
For clean data, this is the same as converting to an integer.
SELECT CONVERT(NUMERIC(10,3),'123.465');
If the scale is too big (the part before the decimal point), you'll get an arithmetic overflow:
SELECT CONVERT(NUMERIC(10,3),'123456789.465');
If the precision is too big (the part after the decimal point), the data is rounded:
SELECT CONVERT(NUMERIC(10,3),'123.123789');
If the data is in another culture, for example in Belgium they use a comma instead of a decimal point, you'll get an error:
SELECT CONVERT(NUMERIC(10,3),'123,123');
This can be resolved by using the REPLACE function:
SELECT CONVERT(NUMERIC(10,3),REPLACE('123,123',',','.'));
However, if there are also thousand separators present, this will result again in an issue:
SELECT CONVERT(NUMERIC(10,3),REPLACE('123.456,123',',','.'));
This is because the intermediate result is 123.456.123 which is not a correct number. We can solve this by either removing the thousand separator first and then replacing the comma.
SELECT CONVERT(NUMERIC(10,3)
,REPLACE(
REPLACE('123.456,123','.','')
,',','.')
);
Keep in mind that when you're converting an entire column of data, one bad row will cause the entire query to fail:
Some rows might be returned though:
To deal with this, you might be tempted to use the ISNUMERIC function. The problem is that this function returns 1 for many examples we might not consider as actually numeric. Even in our example, '943,45' is considered as numeric and the error persists:
Other examples:
SELECT ISNUMERIC('-'),ISNUMERIC('$'),ISNUMERIC('+123')
To learn more about ISNUMERIC, check out the tip Validate Integer and Decimal Values in SQL Server. However, it's best to ignore this function and rather use TRY_CONVERT.
WITH sourcedata AS
(
SELECT Test = '123.456'
UNION ALL
SELECT '789.012'
UNION ALL
SELECT '943,45'
UNION ALL
SELECT '852.14'
UNION ALL
SELECT '78945'
)
SELECT TRY_CONVERT(NUMERIC(10,3),Test)
FROM [sourcedata];
Another option is TRY_PARSE, where we can specify a culture. In this case the culture BE-nl is used, which is for the Dutch speaking part of Belgium.
SELECT TRY_PARSE('943,45' AS NUMERIC(10,3) USING 'BE-nl')
Combining the two functions, we can get to a fully working solution:
WITH sourcedata AS
(
SELECT Test = '123.456'
UNION ALL
SELECT '789.012'
UNION ALL
SELECT '943,45'
UNION ALL
SELECT '852.14'
UNION ALL
SELECT '78945'
)
SELECT
Result = IIF(Result IS NULL
,TRY_PARSE(Test AS NUMERIC(10,3) USING 'BE-nl')
,Result)
FROM(
SELECT
Result = TRY_CONVERT(NUMERIC(10,3),Test)
,Test
FROM [sourcedata]
) tmp;
You can find more info in the tip Handling error converting data type varchar to numeric in SQL Server.
Convert to Date using CONVERT or CAST
The most common use case is to convert a string containing some sort of representation of a date to an actual date data type. In many cases, SQL Server will correctly convert the expression, even if no style is specified:
SELECT Test = CONVERT(DATE,'2021-08-05') UNION ALL SELECT CONVERT(DATE,'August 05 2021') UNION ALL SELECT CONVERT(DATE,'08/05/21') UNION ALL SELECT CONVERT(DATE,'2021-08-05 17:07:53') UNION ALL SELECT CONVERT(DATE,'2021-08-05 07:07:53PM')
However, it's considered best practice to always specify a style to avoid confusion. For example, is 01/02/2021 the first of February (like in most parts of the world), or is it the second of January 2021 (like in one stubborn country)? Depending on the result you need, you select the corresponding style (they are all listed in the documentation):
SELECT Test = CONVERT(DATE,'01/02/2021',101) -- US UNION ALL SELECT CONVERT(DATE,'01/02/2021',103); -- the rest of the world
Converting to a time or datetime is similar as converting to a date; you have to select the appropriate style. You can also convert numbers to a datetime (not a date or datetime2 though). Converting 0 gives you the start date, which is 1900-01-01:
The earliest datetime you can get is 1753-01-01. Going back further will result in an error.
The biggest datetime is 9999-12-31. If you use a decimal number, you also change the time portion:
If you want to convert a "date-like" number to a date, such as the integer 20210805, you'll need to convert it to a string first:
SELECT CONVERT(DATE,CONVERT(CHAR(8),20210805));
More info can be found in the tip SQL Server function to convert integer date to datetime format, or the tip SQL Convert Date to YYYYMMDD for the other way around.
Next Steps
- The cast and convert documentation has a ton of information about all the different types of conversion using CAST or CONVERT in a SQL database. It has a nice compatibility matrix and lists many of the edge cases.
- Other tips that might interest you:
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-09-16
