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 system data type to the data type of the variable.

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.

Implicit and Explicit Conversion

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.

GETDATE() implicitly converts to date style 0. SYSDATETIME() implicitly converts to date style 21.

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 ISO. Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.

The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server 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.

Data type conversion table

Data Type Conversion Behaviors

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.

The following topics describe the conversion behaviors exhibited by their corresponding data types:

binary and varbinary (Transact-SQL)

datetime2 (Transact-SQL)

money and smallmoney (Transact-SQL)

bit (Transact-SQL)

datetimeoffset (Transact-SQL)

smalldatetime (Transact-SQL)

char and varchar (Transact-SQL)

decimal and numeric (Transact-SQL)

sql_variant (Transact-SQL)

date (Transact-SQL)

float and real (Transact-SQL)

time (Transact-SQL)

datetime (Transact-SQL)

int, bigint, smallint, and tinyint (Transact-SQL)

uniqueidentifier (Transact-SQL)

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

Reference

OLE Automation Stored Procedures (Transact-SQL)

CAST and CONVERT (Transact-SQL)

Data Types (Transact-SQL)

COLLATE (Transact-SQL)