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