Conversion Functions
Use the CAST and CONVERT conversion functions to convert expressions of one data type to another data type when data type conversions are not automatically performed by SQL Server 2005. For example, if you compare a char and a datetime expression or a smallint and an int expression, or char expressions of different lengths, SQL Server automatically converts them. This is known as an implicit conversion. Using CAST or CONVERT for these implicit conversions is not necessary. However, you can use CAST and CONVERT in the following conversion scenarios:
- Two expressions are exactly the same data type.
- Two expressions are implicitly convertible.
- Explicitly converting the data types is required.
If you try a conversion that is not possible (for example, converting a char expression that includes letters to int), SQL Server displays an error message.
CAST and CONVERT functions can also be used to obtain a variety of special data formats and can be used in the select list, in the WHERE clause, and anywhere an expression is allowed.
If you want Transact-SQL program code to comply with SQL-92, use CAST instead of CONVERT. Use CONVERT instead of CAST to take advantage of the functionality in CONVERT.
When you use either CAST or CONVERT, the following information is required:
- The expression to convert; for example, a sales report requires the sales data to be converted from monetary data to character data.
- The data type to convert the specified expression to, for example, varchar or any other SQL Server system data type.
A conversion is valid only for the duration of the CAST or CONVERT function, unless you store the converted value.
If you do not specify a length when you convert for the data type, SQL Server automatically supplies a length of 30.
Examples
The following examples use CAST in the first SELECT statement and CONVERT in the second SELECT statement to convert the Title column to an nvarchar(20) column to make the length of the titles shorter.
USE AdventureWorks;
GO
SELECT CAST(Title AS nvarchar(20)) AS Title, Revision
FROM Production.Document
WHERE Revision < 2
GO
Or
USE AdventureWorks;
GO
SELECT CONVERT(nvarchar(20), Title) AS Title, Revision
FROM Production.Document
WHERE Revision < 2
GO
Here is the result set (for either query).
Title Revision
-------------------- --------
Crank Arm and Tire M 0
Front Reflector Brac 1
Installing Replaceme 0
Repair and Service G 0
Training Wheels 2 1
(5 row(s) affected)
In the following example, the HireDate column, a datetime column, is converted to a char(11) column.
USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, CAST(e.HireDate AS char(11)) AS HireDate
FROM HumanResources.Employee e
JOIN Person.Contact c ON e.EmployeeID = c.ContactID
WHERE c.FirstName = 'Kim'
GO
Here is the result set.
FirstName LastName HireDate
--------- ----------------------- -----------
Kim Abercrombie Dec 12 1997
Kim Akers Mar 23 1998
(2 row(s) affected)
See Also
Concepts
Data Type Conversion (Database Engine)
Other Resources
CAST and CONVERT (Transact-SQL)
Functions (Transact-SQL)