Data Type Conversion (Database Engine)

Data Type Conversion (Database Engine)

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.

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








DECLARE @BinaryVariable2 BINARY(2)

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

SELECT CAST( @BinaryVariable2 AS INT)

The final result is 57921, not 123457.


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.

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;
SELECT SalesPersonID,
   CAST(SalesYTD AS varchar(12)),
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%'

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

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



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'


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


Here is the result set.

(1 row(s) affected)

Apr  5 1998 12:00AM            

(1 row(s) affected)


(1 row(s) affected)

Converting float and real Data



Converting money Data



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


Here is the result set.

SM_MONEY VARCHAR               

(1 row(s) affected)


(1 row(s) affected)

Converting decimal and numeric Data





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


decimal, numeric




binary, varbinary, image

One-dimensional Byte() array











money, smallmoney


datetime, smalldatetime


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


Double, Single






String with 4000 characters or less


String with more than 4000 characters


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


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


Community Additions

© 2016 Microsoft