SQL Type Conversions

CAST and CONVERT

Explicitly converts an expression of a data type to another data type. CAST and CONVERT provide similar functionality.

Grammar

Using CAST:

CAST ( expression AS data_type )

Using CONVERT:

CONVERT (data_type[(length)], expression [, style])

Parameters

Expression

Is any valid microsoft®sql Server™ expression. For more information, see expressions.

Data_type

The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type. For more information about the available data types, see Data types.

Length

Optional parameters for nchar,nvarchar,char,varchar,binary , or varbinary data types.

Style

Date format style, whereby datetime or smalldatetime data is converted to character data (nchar,nvarchar,char ,varchar,nchar , or nvarchar data type), or a string format style that will float,real, money or smallmoney data is converted to character data (nchar,nvarchar,char, varchar,nchar , or nvarchar data type).

SQL Server supports data formats in Arabian styles using the Kuwaiti algorithm.

In the table, the two columns on the left represent the style values that convert datetime or smalldatetime to character data. Add 100 to the style value to get a four-bit year (yyyy) that includes century digits.

without century digits (yy)	with century digits (yyyy)	Standard	Input/Output * *
-	0 or 100 (*)	Default value	Mon dd yyyy hh:miam (or PM)
1	101	United States	Mm/dd/yyyy
2	102	Ansi	Yy.mm.dd
3	103	UK/France	Dd/mm/yy
4	104	Germany	Dd.mm.yy
5	105	Italy	Dd-mm-yy
6	106	-	DD Mon yy
7	107	-	Mon dd, yy
8	108	-	Hh:mm:ss
-	9 or 109 (*)	Default value + milliseconds	Mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	United States	Mm-dd-yy
11	111	Japan	Yy/mm/dd
12	112	Iso	Yymmdd
-	13 or 113 (*)	European default + milliseconds	DD Mon yyyy hh:mm:ss:mmm (24h)
14	114	-	Hh:mi:ss:mmm (24h)
-	20 or 120 (*)	ODBC specification	yyyy-mm-dd hh:mm:SS[. FFF]
-	21 or 121 (*)	ODBC specification (with milliseconds)	yyyy-mm-dd hh:mm:SS[. FFF]
-	126 (* *)	ISO8601	YYYY-MM-DD Thh:mm:ss:mmm (no spaces included)
-	130*	Kuwait	DD Mon yyyy hh:mi:ss:mmmAM
-	131*	Kuwait	Dd/mm/yy Hh:mi:ss:mmmAM

* The default value (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, 21, or 121) always returns century digits (yyyy).
* * input when converting to datetime , and output when converting to character data.
specifically for XML. For conversions from datetime or smalldatetime to character data, the output format is shown in the table. For conversions from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversions from Real to character data, the output is equivalent to style 1.

important By default, SQL Server interprets two-digit years based on the cutoff year 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 OLE automation objects, use 2030 as the cutoff year. SQL Server provides a configuration option ("Two-digit cutoff year") to change the cutoff year used by SQL Server and to make the dates consistent. However, the safest option is to specify a four-digit year.

When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros at these locations. When converting from a datetime or smalldatetime value, you can truncate the unwanted date part by using the appropriate char or varchar data type length.

The following table shows the style values when converting from float or real to character data.

value	Output
0(default value)	The maximum number is 6 digits. Use scientific notation as needed.
1	Always a 8-bit value. Always use scientific notation.
2	Always a 16-bit value. Always use scientific notation.

In the following table, left column represents the style value when converting from money or smallmoney to character data.

value	Output
0(default value)	Every three digits to the left of the decimal point are not separated by commas, and two digits to the right of the decimal point, for example 4235.98.
1	A comma separates each three digits to the left of the decimal point and two digits to the right of the decimal point, such as 3,510.92.
2	Every three digits to the left of the decimal point are not separated by commas, and four digits to the right of the decimal point, for example 4235.9819.

return type

Returns the same value as data type 0.

Comments

Implicit conversions are those that do not specify a cast or CONVERT function. Explicit conversions refer to those transformations that have the required cast (convert) function specified. The following diagram shows all explicit and implicit transformations that are available for the data types provided by the SQL Server system, including bigint and sql_variant.

Description Because Unicode data always uses even-bit bytes, a hint is used when converting between binary or varbinary data types and data types supported by Unicode. For example, this conversion does not return a hexadecimal value of 41, but instead returns a hexadecimal value of 4100: SELECT cast (0x41 as nvarchar) as varbinary)

Automatic data type conversions for text and image data types are not supported. The text data can be explicitly converted to character data, and the image data is dominant to binary or varbinary data, but the maximum length is 8000. If you attempt an incorrect conversion (for example, to convert a character expression that contains letters to an int), SQL Server generates an error message.

When the output of CAST or CONVERT is a string and the input is also a string, the output has the same collation and collation label as the input. If the input is not a string, the output takes the default collation of the database and enforces the default collation label. For more information, see Precedence Order of collations.

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST(‘abc‘ AS varchar(5)) COLLATE French_CS_AS

There is no implicit conversion from the sql_variant data type to the assignment, but there is a covert conversion to sql_variant .

Converts a character or binary expression (char,nchar,nvarchar,varchar,binary , or varbinary When converting to an expression of a different data type, the data may be truncated, displayed only as part, or returned with an error because the result is too short to be displayed. In addition to the conversions shown in the following table, convert to char,varchar,nchar,nvarchar,binary , and varbinary will be truncated.

the data type being converted	the data type to convert to	Results
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 is too short to display.
E returns an error because the result length is too short to display.

Microsoft SQL Server only guarantees a round-trip conversion (that is, after converting from the original data type and returning the original data type) to produce the same value across versions. The following example shows a round-trip conversion:

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

For example, do not attempt to construct binary values and convert them to data types categorized by numeric data types. SQL Server does not guarantee that the result of converting decimal or numeric data types to binary is the same between versions of SQL Server.

The following example shows a result expression that cannot be displayed because it is too short.

USE pubsSELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))FROM titlesWHERE type = ‘trad_cook‘

Here is the result set:

Title------------------------- --Onions, Leeks, and Garlic *Fifty Years in Buckingham *Sushi, Anyone?            *(3 row(s) affected)

When a data type with different decimal digits is converted, the value is truncated to the most accurate digit. For example, the result of SELECT CAST (10.6496 as int) is 10.

When converting, the value to be converted is rounded if the number of decimal digits of the target data type is less than the number of decimal digits of the source data type. For example, the result of CAST (10.3496847 as Money) is $10.3497.

When converting char,nchar,varchar , or nvarchar data of non-numeric types to int,float, SQL Server Returns an error message when numeric or decimal . When you convert an empty string ("") to numeric or decimal , SQL Server also returns an error message.

Using binary string data

When binary or varbinary data is converted to character data and an odd digit value is specified after X, SQL Server adds 0 (0) after x to become an even digit value.

Binary data contains characters from 0 to 9 and from a to f (or from A to f), each of which is a group of two characters. Binary strings must start with 0x. For example, to enter FF, type 0xFF. The maximum value is a 8000-byte binary value, and the maximum value for each byte is FF. Binary data types cannot be used for hexadecimal data, but for bit patterns. The conversion and calculation results of hexadecimal numbers stored as binary data cannot be guaranteed to be accurate.

When you specify the length of the binary data type, every two characters are counted as one unit length. A length of 10 indicates that 10 double-character groups will be entered.

A null binary string represented by 0x can be stored as binary data.

Example A. Use both CAST and CONVERT

Each example retrieves the title of the book (the first digit of the current sales for these books is 3) and converts the ytd_sales of those books to Char.

-- Use CAST.USE pubsGOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE ‘3%‘GO-- Use CONVERT.USE pubsGOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CONVERT(char(20), ytd_sales) LIKE ‘3%‘GO

The following is the result set for either query:

Title                          ytd_sales------------------------------ -----------Cooking with Computers: Surrep 3876Computer Phobic AND Non-Phobic 375Emotional Security: A New Algo 3336Onions, Leeks, and Garlic: Coo 375(4 row(s) affected)

B. Using an CAST with arithmetic operators

The following example divides the total cutoff current sales (ytd_sales) by the price of each book by aseparate column calculation (Copies). After rounding to the nearest integer, the result is converted to the int data type.

USE pubsGOSELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS ‘Copies‘FROM titlesGO

Here is the result set:

Copies------20532462622051027440NULL383205NULL1718716204418181263273(18 row(s) affected)

C. Using CAST for concatenation

The following example uses the cast data type conversion function to concatenate non-character, non-binary expressions.

USE pubsGOSELECT ‘The price is ‘ + CAST(price AS varchar(12))FROM titlesWHERE price > 10.00GO

Here is the result set:

------------------The price is 19.99The price is 11.95The price is 19.99The price is 19.99The price is 22.95The price is 20.00The price is 21.59The price is 10.95The price is 19.99The price is 20.95The price is 11.95The price is 14.99(12 row(s) affected)

D. Using CAST for more readable text

The following example uses cast to convert the title column to a char column in the select list, which makes the result easier to read.

USE pubsGOSELECT CAST(title AS char(50)), ytd_salesFROM titlesWHERE type = ‘trad_cook‘GO

Here is the result set:

                                                       ytd_sales--------------------------------------------------     ---------Onions, Leeks, and Garlic: Cooking Secrets of the      375Fifty Years in Buckingham Palace Kitchens              15096Sushi, Anyone?                                         4095(3 row(s) affected)

E. Using a CAST with a LIKE clause

The following example converts an int column (ytd_sales column) to a Char column to use the LIKE clause.

USE pubsGOSELECT title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE ‘15%‘   AND type = ‘trad_cook‘GO

Here is the result set:

title                                                        ytd_sales------------------------------------------------------------ -----------Fifty Years in Buckingham Palace Kitchens                    15096(1 row(s) affected)

Please see

SQL Type Conversions