4 Out-of-the-Box SQL Data Conversion Methods and Use Cases

First of all, you can’t do without them, right?

SQL data conversions or, more specifically, data type conversions are an essential part of a database developer’s or DBA’s regular programming work.

Now, what if your boss signed a contract with another company to provide them with a file in text format coming from your SQL Server database?

This sounds like an exciting challenge!

But you found out that you would have to deal with a date to a string, a number to a string, and a bunch of other SQL data conversions. Are you still up for the challenge?

Not without your arsenal of data conversion tricks!

What’s Available Out-of-the-box?

When I first started T-SQL programming, the first thing I saw that fitted the purpose of conversion was the CONVERT() function.

Besides, the word “convert” is there, right?

Although this may be true, it’s just one of the 4 ways to perform this job. And I was using it for almost ALL of my SQL data conversion. I’m glad I’m way past that. Because I’ve learned that the 4 methods have their own place in your code.

Before we get to the subject of the post, let me present the 4 out-of-the-box methods to perform SQL data conversion:

• CAST()
• CONVERT()
• PARSE()
• TRY_CAST(), TRY_CONVERT(), TRY_PARSE()

Each of the section below will:

• Explain what it is
• Tell you when to use it (use cases)
• Present its limitations
• Give examples and explain it

Everything presented in this article is in plain, simple English as much as possible. By the time you finish reading the entire post, you will know which method is appropriate for a given situation.

So, without any further ado, let’s dive in.

1. SQL Data Conversion Using CAST()

While all of the methods you will see can convert data types, your first choice in converting should definitely be CAST().

Here are the reasons why:

• It’s the fastest running conversion function of all. We will try to prove this later in this post.
• It’s included in SQL-92 language specification standards. So when you need to port your code to other SQL products, like MySQL, the function is also available.

Here’s a very simple syntax for CAST():

CAST( <expression> AS <data_type>[(length)] )

First, let’s examine the syntax:

• <expression> is any valid expression that results in a value that can be converted to the target data type.
• <data_type> is the target data type.
• length is optional and it pertains to the size of the data.

When to Use It

If the only thing you need is to convert a value to another data type, then CAST() is just what you need.

Limitation

On the negative side, CAST() can not give you a formatted output out-of-the-box like a formatted date and time value.

Examples

A. Convert a string to a date:

SELECT CAST('09/11/2004 4:30PM' as datetime2)

And running the above statement will result in:

B. Convert a number to a string:

SELECT CAST(10.003458802 as varchar(max))

And the result of the above conversion is:

Now, if you need something else like formatting the converted data, the next method can help you.

2. SQL Data Conversion Using CONVERT()

The next option of data conversion is to use CONVERT(). As I said before, this is the one I used most in the earlier days.

Here’s the syntax:

CONVERT( <data_type>[(length)], <expression> [, <style>])

From the syntax above, take note that the <style> parameter is optional. And unless you provide it, the function will be similar to CAST().

That’s where my confusion began when I was new to SQL.

When to Use It

If you convert the data with an instant format, then CONVERT() is your friend. Which means you treat the <style> parameter properly.

Limitations

• CAST() is faster than CONVERT(), so if you only need to convert the data, use CAST(). If the output should be formatted, use CONVERT().
• CONVERT() is not an SQL-92 standard, so if you need to port it to other RDBMS, avoid using it.

Examples

A. Convert a date to a string format yyyymmdd

In the following example, I will use the sample database AdventureWorks and transform the [StartDate] column to yyyymmdd:

USE AdventureWorks
GO
SELECT
[BillOfMaterialsID]
,CONVERT(varchar(10), [StartDate],112) as StartDate
FROM [Production].BillOfMaterials]
GO

Note that style 112 is used for formatting dates to yyyymmdd.

B. Convert a number to a string with commas on every 3 digits to the left of the decimal point.

Similarly, the following example will illustrate the AdventureWorks sample database, and we will format the number with commas and with 2 decimal places.

USE AdventureWorks
GO
SELECT
[TransactionID]
,[ProductID]
,CONVERT(varchar(10),[TransactionDate] ,112) as StartDate
,[Quantity]
,CONVERT(varchar(10),[ActualCost],1) as ActualCost
FROM [Production].TransactionHistory
GO

Note that format 1 is used for [ActualCost]. And thanks to CONVERT(), we can format these columns in an instant.

However, what if you need to convert a longer date expression? Will CONVERT() work in that case? Read on to learn about the next method.

3. SQL Data Conversion Using PARSE()

The next method we are going to consider is PARSE().

Check out the syntax:

PARSE( <string value> AS <datatype> [USING <culture>])

When to Use It

• To convert strings to dates or numbers using a specific culture.
• When the string can’t be converted to a date or a number using CAST() or CONVERT(). See the examples for more information.

Limitations

• Conversion is only possible for string to dates and string to numbers
• Relies on the presence of .Net Framework Common Language Runtime (CLR) on the server.
• Is not included in SQL-92 standard specs, so porting to other RDBMS is a problem.
• Has performance overhead when it comes to parsing the string.

Examples

A. Converting a long date string

SELECT PARSE('Monday, June 8, 2020' as datetime USING 'en-US')

The example above is a long date string to be converted to a datetime value using US English culture. And this is where PARSE() will do its best.

That is because the code above will fail if you use CAST() or CONVERT().

B. Converting a money value with a currency symbol

SELECT PARSE('€1024,01' as money using 'de-DE')

Now, try to do the conversion using CAST() and CONVERT()

SELECT CONVERT(money,'€1024,01')
SELECT CAST('€1024,01' as money)

The statement won’t throw errors, still, have a look at this unexpected result:

As a result, the value has been converted to 102401.00 instead of 1024.01.

So far, we have discovered that the first 3 methods are prone to errors unless you check them. Nevertheless, the 4th method can be your solution to the faulty result.

4. SQL Data Conversion using TRY_CAST(), TRY_CONVERT(), or TRY_PARSE()

Finally, the last method for SQL data conversion is using a variant of the first 3 but with a prefix of TRY_.

But even so, what’s the difference?

They have the same parameters as the previous 3 without the TRY_ prefix. But the difference is, they return NULL if the value can’t be converted. Now, if the value can’t be converted by any of the 3 explicitly, an error occurs. See the examples below for a better understanding.

When to Use It

You can use any of the 3 with conditional statements like CASE WHEN or IIF to test for errors.

Limitations

The 3 of them have the same limitations as the ones without the TRY_ prefix, except for values that can’t be converted.

Examples

A. Use TRY_CAST() to test if conversion will succeed using IIF:

SELECT IIF(TRY_CAST('111b' AS real) IS NULL, 'Cast failed', 'Cast succeeded') AS Result

The code above will return ‘Cast Failed’ because ‘111b’ can’t be converted to real. Take away the ‘b’ from the value, and it will return ‘Cast succeeded’.

B. Using TRY_CONVERT() on dates with a specific format

SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result

This will return NULL because the format uses dmy or day-month-year. And the input expression for TRY_CONVERT() is in the format of mdy or month-day-year. The error was triggered because the month value is 31.

C. Using TRY_PARSE() that will generate a runtime error

SELECT
CASE WHEN TRY_PARSE('10/21/2133' AS smalldatetime USING 'en-US') IS NULL
    THEN 'True'
    ELSE 'False'
END AS Result

This code will generate a runtime error as seen below:

That’s it for the 4 out-of-the-box methods in SQL data conversion. But there’s more to come.

How About SQL Data Conversion Using Implicit Conversion?

Now let’s consider implicit conversion. This is a silent method.

Why silent?

Because you might be doing it already, but you’re unaware of it. Or at least, you know it’s happening, but you’re ignoring it.

In other words, this is the type of conversion that SQL automatically does without any functions.

Let me give you an example:

DECLARE @char CHAR(25)
DECLARE @varchar VARCHAR(25)
DECLARE @nvarchar NVARCHAR(25)
DECLARE @nchar NCHAR(25)
 
SET @char = 'Live long and prosper'
SET @varchar = @char
SET @nvarchar = @varchar
SET @nchar = @nvarchar
 
SELECT @char AS [char], @varchar AS [varchar], @nvarchar AS [nvarchar], @nchar AS [nchar]

The above code will be executed successfully. Try it out yourself, and you will have a similar result to the one below:

Let’s try this with dates:

DECLARE @datetime datetime
DECLARE @smalldatetime smalldatetime
DECLARE @datetime2 datetime2

SET @datetime = '12/31/2050 14:34'
SET @smalldatetime = @datetime
SET @datetime2 = @smalldatetime

SELECT @datetime as [datetime], @smalldatetime as [smalldatetime], @datetime2 as [datetime2]

As expected, this will yield a successful result:

Let’s try it this time with numbers:

DECLARE @int int
DECLARE @real real
DECLARE @decimal decimal
DECLARE @float float

SET @int = 1701
SET @real = @int
SET @decimal = @real
SET @float = @decimal

SELECT @int as [int], @real as [real], @decimal as [decimal], @float as [float]

Still a success, right?

So far, we have used simple values that will be suitable for a rather similar type of data. Let’s get into the next level: numbers to strings.

DECLARE @number int
DECLARE @string varchar(5)

SET @number = 1701
SET @string = @number

SELECT @number as [number], @string as [string]

This will be converted successfully as you can see from the result below:

There are many more instances when SQL Server will try to “guess” how to convert data. As you can see from this reference, there are many instances compared to the ones that require explicit conversion.

Since SQL Server allows this, does this mean you can freely allow this to happen all over your code?

Caveats in Implicit Conversion

For one thing, it may be convenient. But once the limits of each data type are reached, you will realize that implicit conversion is a bit dangerous if left unchecked.

Consider an example below:

DECLARE @char char(25)
DECLARE @varchar varchar(25)
DECLARE @nvarchar nvarchar(25)
DECLARE @nchar nchar(25)

SET @nvarchar = N'I ❤ U!'
SET @nchar = @nvarchar 
SET @char = @nchar
SET @varchar = @nchar

SELECT @char as [char], @varchar as [varchar], @nvarchar as [nvarchar], @nchar as [nchar]

Did you see the emoji value? That will count as a unicode value.

While all the statements above will run successfully, the variables with non-unicode types like varchar and char will have unexpected results. See the result below:

Nonetheless, that’s not the only problem. Errors will pop out when the value becomes out-of-range. Consider an example with dates:

DECLARE @datetime datetime
DECLARE @smalldatetime smalldatetime
DECLARE @datetime2 datetime2

SET @datetime = '12/31/2374 14:34'
SET @smalldatetime = @datetime
SET @datetime2 = @smalldatetime

SELECT @datetime as [datetime], @smalldatetime as [smalldatetime], @datetime2 as [datetime2]

The assignment of the datetime value to the smalldatetime variable will trigger the error as you can see below:

But there’s another caveat, which you should also be aware of when dealing with implicit conversion: performance overhead. Seeing that this is a hot topic, it deserves to have a separate section.

Performance Implications of Different SQL Data Conversion Methods

Believe it or not, different SQL data conversion methods will have different performance in actual situations. And you should at least be aware of this so you can avoid performance pitfalls.

How CAST(), CONVERT() and PARSE() Perform

First, let’s examine how CAST(), CONVERT(), and PARSE() perform under natural conditions by comparing which is faster. We adapt and prove the concept of our example taken from here. Consider the code below:

USE AdventureWorks
GO

SET STATISTICS TIME ON
SELECT CAST([NationalIDNumber] as int) FROM [HumanResources].[Employee]
SELECT CONVERT(int,[NationalIDNumber]) FROM [HumanResources].[Employee]
SELECT PARSE([NationalIDNumber] as int) FROM [HumanResources].[Employee]
SET STATISTICS TIME OFF
GO

Now, let’s examine the code which uses the AdventureWorks database from Microsoft:

• SET STATISTICS TIME ON will output the CPU time and elapsed time in each of the SELECT statements
• Then, the column we choose to convert for demonstration purposes is [NationalIDNumber], which has a type of nvarchar(15).
• Also, the conversion is from a string to an integer: nvarchar(15) to int.
• And last, we restore the SET STATISTICS TIME to its previous value

Notice the output in the Messages tab of the Query result:

Here’s what we have come up with using this example:

• It proves that CAST() performs the fastest (1 ms.) and PARSE() performs the slowest (318 ms.).
• We follow this precedence when deciding which function to use to convert data: (1) CAST() (2) CONVERT() (3) PARSE().
• Remember when each function is relevant and consider the limitations when deciding which function to use.

How Implicit Conversion Performs

At this point, you should be able to see that I recommend the use of functions like CAST() to convert data. And in this section, you will see why.

Consider this query using the WideWorldImporters database from Microsoft. Before executing it, please enable the Include Actual Execution Plan in SQL Server Management Studio.

USE WideWorldImporters
GO
SELECT
[CustomerID]
,[OrderID]
,[OrderDate]
,[ExpectedDeliveryDate]
FROM [Sales].[Orders]
WHERE [CustomerID] like '487%'

In the query above, we filter the result of sales orders with [CustomerID] like ‘487%’. Thфе is just to demonstrate what effect the implicit conversion of an int data type has on varchar.

Next, we examine the execution plan below:

As you can see, there’s a warning in the SELECT icon. Therefore, hover your mouse to see the tooltip. Next, notice the warning message, namely the CONVERT_IMPLICIT.

Before we continue, this CONVERT_IMPLICIT warning occurs when it is necessary to perform an implicit conversion for SQL Server. Let’s have a closer look at the problem. As described below, the warning has 2 parts:

• CONVERT_IMPLICIT may affect “CardinalityEstimate” in a query plan choice.
• CONVERT_IMPLICIT may affect “SeekPlan” in a query plan choice.

Both of them indicate that your query will run slower. But we know why, of course. We intentionally force an implicit conversion by using a LIKE operator for an integer value.

What is the point in that?

• Implicit conversion of data causes SQL Server to use CONVERT_IMPLICIT, which slows down your query execution.
• To fix this problem, eliminate the use of implicit conversion. In our case, we used [CustomerID] LIKE ‘487%’, we can fix it by changing [CustomerID] = 487. Fixing the query will change the query execution plan, remove the warning earlier, and change the index scan operator to an index seek. In the end, performance improves.

Happy ending? Yes!

Takeaways

As shown, we can’t just let SQL Server do the converting with an implicit conversion. I recommend you to follow the precedence when it comes to deciding what to use when converting data.

• Firstly, if you just need to convert as is, use CAST(). It’s a more standardized function when porting to other RDBMs is concerned.
• Secondly, if you need formatted data, use CONVERT().
• Thirdly, if both CAST() and CONVERT() fail to do the job, use PARSE().
• Lastly, to test for errors when converting, use TRY_CAST(), TRY_CONVERT(), or TRY_PARSE().

Well, that’s all for now. I hope this helps you with your next coding adventures. Break a leg!

To learn more on the topic of SQL data conversion from Microsoft:

• CAST and CONVERT
• PARSE
• TRY_CAST, TRY_CONVERT, and TRY_PARSE

Tags: sql data conversion, sql server Last modified: September 20, 2021