Table of contents
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 Standard cost. 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. Here is the order of precedence for the common data types we have previously covered:
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:
- 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.
- 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 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 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:
- 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.
- The GETDATE() function is used to return the current date.
- 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 yeas 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 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.
Leave a Reply