expression

Is any valid expression.

data_type

Is the target system-supplied data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types. For CONVERT, if length is not specified, the default to 30 characters.

style

Is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or to convert character data of known date or time formats to datetime or smalldatetime data; or the string format used to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). When style is NULL, the result returned is also NULL.

Note:

The styles listed in this topic are supported by SQL Server in the described combinations with CONVERT target data types. All other styles and combinations are not supported. Do not use any unsupported styles. Using an unsupported style or an unsupported combination of style and target data type might return an error or unreliable results. These results are not guaranteed across releases of SQL Server.

SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

In the following table, the two columns on the left represent the style values for converting datetime or smalldatetime data to character data. Add 100 to a style value to obtain a four-place year that includes the century (yyyy).

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1, 2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106 (1)	-	dd mon yy
7	107 (1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109 (1, 2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (1, 2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130 (1, 2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.

5 Hijri is a calendar system with several variations. SQL Server 2005 uses the Kuwaiti algorithm.

Important:

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.

6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.

The following table shows the values for style that can be used for converting float or real to character data.

Value	Output
0 (default)	A maximum of 6 digits. Use in scientific notation, when appropriate.
1	Always 8 digits. Always use in scientific notation.
2	Always 16 digits. Always use in scientific notation.

Note:
If style 126 is specified for conversion from float and real the output is equivalent to the style value of 2 and 1, respectively.

The following table shows the values for style that can be used for converting money or smallmoney to character data.

Value	Output
0 (default)	No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1	Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2	No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

Note:
If style 126 is specified for conversion from money, or smallmoney to character data, the output is equivalent to the style value of 2.

The following table shows the values for style that can be used for converting string input to xml data.

Value	Output
0 (default)	Use default parsing behavior that discards insignificant white space and does not allow for an internal DTD subset. When you convert to the xml data type, SQL Server 2005 insignificant white space is handled differently than in XML 1.0. For more information, see Generating XML Instances. Note:
1	Preserve insignificant white space. This style setting sets the default xml:space handling to behave the same as if xml:space="preserve" has been specified instead.
2	Enable limited internal DTD subset processing. If enabled, the server can use the following information that is provided in an internal DTD subset to perform nonvalidating parse operations. Defaults for attributes are applied. Internal entity references are resolved and expanded. The DTD content model will be checked for syntactical correctness. The parser will ignore external DTD subsets. It also does not evaluate the XML declaration to see whether the standalone attribute is set yes or no, but instead parses the XML instance as if it is a stand-alone document.
3	Preserve insignificant white space and enable limited internal DTD subset processing.

Returns the same value as data_type.

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server 2005 system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

Note:
Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. For example, the following conversion does not return a hexadecimal value of 41; it returns 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Large-value Data Types

xml Data Type

text and image Data Types

Output Collation

Truncating and Rounding Results

When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

From data type	To data type	Result
int, smallint, or tinyint	char	*
	varchar	*
	nchar	E
	nvarchar	E
money, smallmoney, numeric, decimal, float, or real	char	E
	varchar	E
	nchar	E
	nvarchar	E

* = Result length too short to display. E = Error returned because result length is too short to display.

SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from version to version. The following example shows such a roundtrip conversion:

DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

Note:
Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server.

The following example shows a resulting expression that is too small to display.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5

Here is the result set.

FirstName      LastName         Title                  Sick Leave
---------      ---------      -------------------   -----------
Gustavo        Achong         Mr.                   *
Catherine      Abel           Ms.                   *
Kim            Abercrombie    Ms.                   *
Humberto       Acevedo        Sr.                   *
Pilar          Ackerman       Sra.                  *

(5 row(s) affected)

When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The following table shows the behavior.

From	To	Behavior
numeric	numeric	Round
numeric	int	Truncate
numeric	money	Round
money	int	Round
money	numeric	Round
float	int	Truncate
float	numeric	Round
float	datetime	Round
datetime	int	Round

For example, the result of the following conversion is 10:

SELECT CAST(10.6496 AS int)

When you convert data types in which the target data type has fewer decimal places than the source data type, the value is rounded. For example, the result of the following conversion is $10.3497:

SELECT CAST(10.3496847 AS money)

SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

Converting Binary String Data

A. Using both CAST and CONVERT

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

B. Using CAST with arithmetic operators

USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson 
WHERE CommissionPCT != 0;
GO

Computed      
------ 
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107

(14 row(s) affected)

C. Using CAST to concatenate

USE AdventureWorks;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO

ListPrice
------------------
The list price is 357.06
The list price is 364.09
The list price is 364.09
The list price is 364.09
The list price is 364.09

(5 row(s) affected)

D. Using CAST to produce more readable text

USE AdventureWorks;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Name       UnitPrice
---------- ---------------------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99

(3 row(s) affected)

E. Using CAST with the LIKE clause

USE AdventureWorks;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID
FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

FirstName        LastName            SalesYTD         SalesPersonID
---------------- ------------------- ---------------- -------------
Carol            Elliott             2811012.7151      279
Julie            Estes               219088.8836       288
Janeth           Esteves             2241204.0424      289

(3 row(s) affected)

F. Using CONVERT or CAST with typed XML

CONVERT(XML, '<root><child/></root>')

CONVERT(XML, '<root>          <child/>         </root>', 1)

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

G. Using CAST and CONVERT with datetime data

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

SELECT 
   '2006-04-04T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-04T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-04T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

Reference

Other Resources

Help and Information