Jaa

Data Type Conversion (Database Engine)

  • Artikkeli

Data types can be converted in the following scenarios:

  • When data from one object is moved to, compared with, or combined with data from another object, the data may have to be converted from the data type of one object to the data type of the other.
  • When data from a Transact-SQL result column, return code, or output parameter is moved into a program variable, the data must be converted from the SQL Server 2005 system data type to the data type of the variable.

Data types can be converted either implicitly or explicitly:

  • Implicit conversions are not visible to the user.
    SQL Server automatically converts the data from one data type to another. For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

  • Explicit conversions use the CAST or CONVERT functions.
    The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. For example, the following CAST function converts the numeric value of $157.27 into a character string of '157.27':

    CAST ( $157.27 AS VARCHAR(10) )

    Use CAST instead of CONVERT if you want Transact-SQL program code to comply with SQL-92. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.

Some implicit and explicit data type conversions are not supported when you are converting the data type of one SQL Server object to another. For example, an nchar value cannot be converted to an image value. An nchar can only be converted to binary by using explicit conversion, an implicit conversion to binary is not supported. However, an nchar can be explicitly or implicitly converted to nvarchar.

When handling the sql_variant data type, SQL Server supports implicit conversions of objects with other data types to the sql_variant type. However, SQL Server does not support implicit conversions from sql_variant data to an object with another data type.

For more information about supported conversions between SQL Server objects, see CAST and CONVERT (Transact-SQL).

When you convert between an application variable and a SQL Server result set column, return code, parameter, or parameter marker, the supported data type conversions are defined by the database API. For more information, see Moving Data to Program Variables.

Data Type Conversion Behaviors

The following sections in this topic describe the conversion behaviors exhibited by the following data types:

binary and varbinary data

float and real data

bit data

money data

Character data

decimal and numeric data

datetime and smalldatetime data

Data types by using OLE Automation stored procedures

Converting binary and varbinary Data

When data is converted from a string data type (char, varchar, nchar, nvarchar, binary, varbinary, text, ntext, or image) to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.

Converting data to the binary and varbinary data types is useful if binary data is the easiest way to move data around. Converting any value of any type to a binary value of large enough size and then back to the type, will always result in the same value if both conversions are taking place on the same version of SQL Server. The binary representation of a value might change from version to version of SQL Server.

You can convert int, smallint, and tinyint to binary or varbinary, but if you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. For example, the following SELECT statement shows that the integer value 123456 is usually stored as a binary 0x0001e240:

SELECT CAST( 123456 AS BINARY(4) )

However, the following SELECT statement shows that if the binary target is too small to hold the entire value, the leading digits are silently truncated so that the same number is stored as 0xe240:

SELECT CAST( 123456 AS BINARY(2) )

The following batch shows that this silent truncation can affect arithmetic operations without raising an error:

DECLARE @BinaryVariable2 BINARY(2)

SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1

SELECT CAST( @BinaryVariable2 AS INT)
GO

The final result is 57921, not 123457.

Note

Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server.

Converting to bit Data

Converting to bit promotes any nonzero value to 1.

Converting Character Data

When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated.

When a character expression is converted to a character expression of a different data type or size, such as from char(5) to varchar(5), or char(20) to char(15), the collation of the input value is assigned to the converted value. If a noncharacter expression is converted to a character data type, the default collation of the current database is assigned to the converted value. In either case, you can assign a specific collation by using the COLLATE clause.

Note

Code page translations are supported for char and varchar data types, but not for text data type. As with earlier versions of SQL Server, data loss during code page translations is not reported.

Character expressions that are being converted to an approximate numeric data type can include optional exponential notation (a lowercase e or uppercase E followed by an optional plus (+) or minus (-) sign and then a number).

Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators, such as the thousands separator in 123,456.00, are not allowed in the string.

Character expressions being converted to money or smallmoney data types can also include an optional decimal point and dollar sign ($). Comma separators, as in $123,456.00, are allowed.

The following example shows how to convert data for display. This example converts sales data to character data before performing a string comparison and converts the current date to style 3, dd/mm/yy.

USE AdventureWorks;
GO
SELECT SalesPersonID,
   CAST(SalesYTD AS varchar(12)),
   CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%'
GO

This example converts a uniqueidentifier value to a char data type.

DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char'
GO

This example converts the current date to style 3, dd/mm/yy.

SELECT CONVERT(char(12), GETDATE(), 3)
GO

Converting datetime and smalldatetime Data

When you convert to datetime, SQL Server 2005 rejects all values it cannot recognize as dates. These include dates earlier than January 1, 1A.D. You can convert datetime values to smalldatetime when the date is in the correct range: from January 1, 1900 through June 6, 2079. The time value is rounded to the nearest minute.

The following example converts smalldatetime and datetime values to varchar and binary data types, respectively.

DECLARE @mydate_sm smalldatetime
SET @mydate_sm = '4/05/98'

SELECT CAST(@mydate_sm AS varchar) AS SM_DATE_VARCHAR
GO

DECLARE @mydate  datetime
SET @mydate     = '4/05/98'

SELECT  CAST(@mydate AS binary) AS DATE_BINARY
GO

Here is the result set.

(1 row(s) affected)

SM_DATE_VARCHAR                
------------------------------ 
Apr  5 1998 12:00AM            

(1 row(s) affected)

DATE_BINARY                                                    
-------------------------------------------------------------- 
0x0000000000000000000000000000000000000000000000008c3000000000 

(1 row(s) affected)

Converting float and real Data

Values of float are truncated when they are converted to any integer type.

When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Functions (Transact-SQL).

Converting money Data

When you convert to money from integer data types, units are assumed to be in monetary units. For example, the integer value of 4 is converted to the money equivalent of 4 monetary units.

The following example converts smallmoney and money values to varchar and decimal data types, respectively.

USE AdventureWorks;
GO
DECLARE @mymoney_sm smallmoney;
SET  @mymoney_sm = 3148.29;
SELECT  CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar';
GO
DECLARE @mymoney    money;
SET  @mymoney    = 3148.29;
SELECT  CAST(@mymoney AS decimal)    AS 'MONEY DECIMAL';

GO

Here is the result set.

SM_MONEY VARCHAR               
------------------------------ 
3148.29                        

(1 row(s) affected)

MONEY DECIMAL          
---------------------- 
3148                   

(1 row(s) affected)

Converting decimal and numeric Data

For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. However, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale is not sufficient to raise an error.

Converting Data Types by Using OLE Automation Stored Procedures

Because SQL Server uses Transact-SQL data types and OLE Automation uses Visual Basic data types, the OLE Automation stored procedures must convert the data that passes between them.

The following table describes SQL Server to Visual Basic data type conversions.

SQL Server data type Visual Basic data type

char, varchar, text, nvarchar, ntext

String

decimal, numeric

String

bit

Boolean

binary, varbinary, image

One-dimensional Byte() array

int

Long

smallint

Integer

tinyint

Byte

float

Double

real

Single

money, smallmoney

Currency

datetime, smalldatetime

Date

Anything set to NULL

Variant set to Null

All single SQL Server values are converted to a single Visual Basic value with the exception of binary, varbinary, and image values. These values are converted to a one-dimensional Byte() array in Visual Basic. This array has a range of Byte(0 to length1) where length is the number of bytes in the SQL Server binary, varbinary, or image values.

These are the conversions from Visual Basic data types to SQL Server data types.

Visual Basic data type SQL Server data type

Long, Integer, Byte, Boolean, Object

int

Double, Single

float

Currency

money

Date

datetime

String with 4000 characters or less

varchar/nvarchar

String with more than 4000 characters

text/ntext

One-dimensional Byte() array with 8000 bytes or less

varbinary

One-dimensional Byte() array with more than 8000 bytes

image

See Also

Concepts

Moving Data to Program Variables

Other Resources

OLE Automation Stored Procedures (Transact-SQL)
STR (Transact-SQL)
Functions (Transact-SQL)
CAST and CONVERT (Transact-SQL)
Data Types (Transact-SQL)
COLLATE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance