SQL Data Conversions

Implicit Data Conversion

Implicit conversions are those conversions that occur automatically whenever the CAST or CONVERT functions aren’t used. Not all values can be implicitly converted to another data type. The following chart shows what can be implicitly converted for the common data types we’ve previously covered:

Please keep in mind this chart shows what implicit conversions are possible. It doesn’t guarantee all values from one type will convert to another. For example, the VARCHAR value ‘The car broke down’ never makes a good DATETIME.

An example of an implicit data conversion occurs when we work with percentages and INT data types.

In this example, we’re multiplying Quantity by StandardCost. One is an SMALLINT value, which has no decimal places, and the other is MONEY, which does.

SELECT P.Name,
       I.Quantity,
       P.StandardCost,
       I.Quantity * P.StandardCost as TotalCost
FROM   Production.ProductInventory I
       INNER JOIN Production.Product P
       ON P.ProductID = I.ProductID
WHERE  P.StandardCost > 0.00

When you review the results, you see that total cost has decimal places?

In this case, the Quantity was first converted to a MONEY datatype before the TotalCost was calculated.

You may be wondering why Quantity was converted to Money and not StandardCost to SMALLINT.

The reason is that order values are implicitly converted from one data type to another is determined by type precedence.

Data type precedence determines the direction or order implicit datatype occur.

Note: With 30 or more supported data types, the official list is longer.

Data types of lower precedence will attempt to convert to one of higher precedence, but not the other way around.

Consider:

SELECT 100 * .5

This statement returns 50.0. The reason is that .5 is a higher precedence, so SQL converts 100, which is an INT type, to the higher precedence. Since converting from 100 to 100.00 is an allowed implicit conversion, it occurs without error.

Now consider:

SELECT 'Today is ' + GETDATE()

The result we’re aiming for is ‘Today is 2015-07-02 08:01:54.932’ or something similar, yet the statement returns this error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

You would think this statement would return a result since it seems it would not be difficult to convert the current data and time to a text value, but because DATETIME has a higher precedence than any textual value, SQL is trying to convert the text to a date.

‘Today is ‘ isn’t a valid date, SQL doesn’t know where that exists on any calendar, so throws an error.

There are really two factors that take place to determine whether an item can be implicitly converted to a data type:

1. Can a value be converted from one data type to another? Implicit conversion isn’t supported for all data types, so understanding whether certain combinations work, is important to understand.
2. What is the data type precedence? If the value’s data type is already at a higher precedence, it won’t be implicitly converted to a lower one.

Explicit Type Conversions with CAST AND CONVERT

It isn’t always possible to combine datatypes together in an expression without causing a server error.

When this occurs, then we need to explicitly convert one of the data types to another to avoid an error, which can do this with one of the SQL conversion functions, CAST and CONVERT.

An example would be when working with dates. Suppose we want to query that returns text regarding employee birthdays.

If we try to execute the following command, it fails:

SELECT P.FirstName + ' ' + P.LastName,
       'Birth Date ' + E.BirthDate
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P
       ON P.BusinessEntityID = E.BusinessEntityID

It returns the error:

Msg 402, Level 16, State 1, Line 1
The data types varchar and date are incompatible in the add operator.

The problem is the expression:

'Birth Date'+ E.BirthDate

The column BirthDate data type is DateTime. Here, we’re mixing text with dates. In order to fix this, we must first convert the DateTime data type into a text value.

We can use the CAST statement to convert the BirthDate into a VARCHAR value. This statement run with no errors:

SELECT P.FirstName + ' ' + P.LastName,
       'Birth Date ' + CAST(E.BirthDate as VARCHAR)
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P
       ON P.BusinessEntityID = E.BusinessEntityID

There are two commands we can use to do so: CAST and CONVERT.

The main difference between CAST and CONVERT is that CONVERT also allows you to define a format for the converted value. This is handy when, for instance, converting DATETIME values to VARCHAR. You can convert the date into a more human readable format. We’ll take more about this in a following section.

You just saw how we used CAST to convert a value from one datatype to another. Let's explore further.

CAST

The CAST SQL conversion function is used to convert from a value from one data type to another. It is a ANSI SQL-92 compliant function.

The syntax for the function is:

CAST(value as datatype)

where value is the item you wish to convert, and datatype is the type you which to convert the value to.

In previous section’s example CAST(E.BirthDate as VARCHAR) converted the DATETIME column BirthDate to a VARCHAR text value.

When converting from a DATATYPE value to VARCHAR, I don’t usually use CAST, as I normally want to format the value; however, I do use CAST when I just need to do conversion. This occurs when I want to:

• Convert a VARCHAR or other text value to a numeric or DATETIME value for calculations.
• Need to convert numeric values to the same type, such as when working with INT and FLOAT values.

Consider the example. The production manager would like to reduce on hand inventory quantities by 10%. What are the new amounts?

To start well, use this query:

SELECT   I.ProductID,
         I.LocationID,
         I.Shelf,
         I.Bin,
         I.Quantity,
         I.Quantity * .90 as ReducedQuantity
FROM     Production.ProductInventory I
ORDER BY I.ProductID, I.LocationID

You hand these results to the production manager:

But notice the ReducedQuantity column. There are decimal places! The production manager complains about this. In an exasperated tone, she mentions to you that it’s obvious we can’t have ½ a bike on stock, so why is report showing them!

What to do? Use CAST to convert the calculated value back to an integer.

We can use the following query to do so:

SELECT   I.ProductID,
         I.LocationID,
         I.Shelf,
         I.Bin,
         I.Quantity,
         CAST(I.Quantity * .90 as SMALLINT) as ReducedQuantity
FROM     Production.ProductInventory I
ORDER BY I.ProductID, I.LocationID

You hand the following result to the manager who deems them acceptable:

In this example, I used to case the quantity to SMALLINT as the column data type is SMALLINT, I could have cast it to INT, but want to keep the types consistent.

CONVERT

The CONVERT SQL conversion function is used to convert from a value from one data type to another with formatting. It is specific to SQL Server and not an ANSI SQL-92 compliant function.

The syntax for the function is:

CONVERT(datatype, value, style)

where:

• datatype is the type you which to convert the value to
• value is the item you wish to convert
• style is the format you which to see converted values stated as.

Where the CONVERT function really shines is when you want to display date or numeric values in text. We’ll focus on converting dates. Once you get the hang of this, then converting numeric values is a cinch.

CONVERT DATETIME TYPES

Consider the following:

SELECT 'Today''s date is ' + CAST(GETDATE() as varchar)

which returns the result.

Today’s date is Jul 4 2015 10:35AM

There are three things to notice:

1. I used two single quotes to embed a single quote in the text value. This is called “escaping” the character (see Constants, Transact-SQL). We need to do this so SQL doesn’t think the single quote is signaling the end of the text value.
2. The GETDATE() function is used to return the current date.
3. The date isn’t in the best format for reading.

So how can we fix the format? We can use CONVERT with a style.

Check out:

SELECT 'Today''s date is ' + CONVERT(VARCHAR, GETDATE(), 101)

which returns the result.

Today’s date is 07/04/2015

Much better!

The “101” in the CONVERT is the style. There are many more styles are listed on MSDN, but some of the more popular ones to use with dates are:

Note: You’ll notice there are two styles per listing. This is so you have the choice to display dates with or without the century. Single digit styles display the years as two digits (yy); whereas, those in the hundreds display years as four digits (yyyy).

Remember the style is optional with convert.

Summary

Both CAST and CONVERT are used to switch a value from one data type to another. CAST is ANSI SQL-92 compliant, so is useful when you’re going to write SQL that need to execute on different DBMS such as Oracle, MySQL, or SQL Server.

CONVERT is not ANSI SQL-92 compliant. Its advantage is that you can specify styles which aid in formatting converted values. These are handy, especially when working with dates, when converting values to text.

Use CAST rather than CONVERT if you want Transact-SQL program code to comply with SQL-92. Use CONVERT rather than CAST to take advantage of the style functionality in CONVERT.