Learn how to convert data with SQL CAST and SQL CONVERT


By:   |   Updated: 2021-09-16   |   Comments (1)   |   Related: More > TSQL


Master Your Data Environment With DataOps

Free MSSQLTips Webinar: Master Your Data Environment With DataOps

Learn tips and tricks on how to master your data environment with SolarWinds® DataOps solutions, which are designed to help you streamline essential database tasks, database projects, and data-centric application development. Let us show you how to save time on tedious but critical database documentation tasks.


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:

simple cast and convert example

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);
convert with style

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:

convert date with style

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
convert with explicit length

However, if we don't specify the length, we see that a piece of the string is truncated:

convert with default length

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;
default of length of 1 when length isn

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):

convert date to string with style

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);
* returned because of truncate

In the case of Unicode data types an error is returned instead:

SELECT CONVERT(NCHAR(1),132456);
unicode error because of truncate

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');
convert to int

Converting a decimal number will result in an error if integer is the destination data type:

SELECT CONVERT(INT,'123.123');
error converting decimal data to int

However, it's no problem if the source data type is numeric (or decimal):

SELECT CONVERT(INT,123.923);
convert to int with truncate

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');
convert to numeric

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');
convert to numeric overflow

If the precision is too big (the part after the decimal point), the data is rounded:

SELECT CONVERT(NUMERIC(10,3),'123.123789');
convert to numeric with rounding

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');
error because of comma

This can be resolved by using the REPLACE function:

SELECT CONVERT(NUMERIC(10,3),REPLACE('123,123',',','.'));
replace comma with decimal point

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','.','')
            ,',','.')
        );
dealing with thousand separator

Keep in mind that when you're converting an entire column of data, one bad row will cause the entire query to fail:

entire query fails because of bad data

Some rows might be returned though:

some rows are 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:

still error with isnumeric

Other examples:

SELECT ISNUMERIC('-'),ISNUMERIC('$'),ISNUMERIC('+123')
weird isnumeric results

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];
try_convert ftw

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;
all numbers converted

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')
converting to date without style

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
multiple date styles

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:

convert int to datetime

The earliest datetime you can get is 1753-01-01. Going back further will result in an error.

earliest date

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





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: 2021-09-16

Comments For This Article




Thursday, September 16, 2021 - 10:41:32 AM - Aaron Bertrand Back To Top (89238)
Re: FORMAT(), please keep in mind that the performance overhead of invoking CLR is significant. You won't notice when you're doing a simple assignment of a variable or in a small set, but at scale it is about twice as expensive as CONVERT():

https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but


download














get free sql tips
agree to terms