Datatype conversion functions

Datatype conversions change an expression from one datatype to another, and reformat the display format for date and time information.

Adaptive Server performs certain datatype conversions, called implicit conversions. For example, if you compare a char expression and a datetime expression, or a smallint expression and an int expression, or char expressions of different lengths, Adaptive Server automatically converts one datatype to another.

For other conversions, called explicit conversions, you must use a datatype conversion function to make the datatype conversion. For example, before concatenating numeric expressions, you must convert them to character expressions. If you attempt to explicitly convert a date to a datetime and the value is outside the datetime range such as “Jan 1, 1000” the conversion is not allowed and an error message appears. See “Using the convert function for explicit conversions”.

Some datatype conversions are not allowed, either implicitly or explicitly. For example, you cannot convert smallint or binary data to datetime. Table 16-3 and Table 16-4 show whether individual datatype conversions are performed implicitly, explicitly, or are not supported in the tables.

Table 16-3: Explicit, implicit, and unsupported datatype conversions

From

binary

varbinary

bit

[n]char

[n]varchar

datetime

smalldatetime

bigdatetime

bigtime

tinyint

smallint

unsigned smallint

int

unsigned int

binary

I

I

I

I

U

U

I

I

I

I

I

I

I

varbinary

I

I

I

I

U

U

I

I

I

I

I

I

I

bit

I

I

I

I

U

U

U

U

I

I

I

I

I

[n]char

I

I

E

I

I

I

I

I

E

E

E

E

E

[n]varchar

I

I

E

I

I

I

I

I

E

E

E

E

E

datetime

I

I

U

I

I

I

I

I

U

U

U

U

U

smalldatetime

I

I

U

I

I

I

I

I

U

U

U

U

U

bigdatetime

I

I

U

I

I

I

I

-

I

U

U

U

U

U

bigtime

I

I

U

I

I

I

I

I

-

U

U

U

U

U

tinyint

I

I

I

E

E

U

U

U

U

I

I

I

I

smallint

I

I

I

E

E

U

U

U

U

I

I

I

I

unsigned smallint

I

I

I

E

E

U

U

U

U

I

I

I

I

int

I

I

I

E

E

U

U

U

U

I

I

I

I

unsigned int

I

I

I

E

E

U

U

U

U

I

I

I

I

bigint

I

I

I

E

E

U

U

U

U

I

I

I

I

I

unsigned bigint

I

I

I

E

E

U

U

U

I

I

I

I

I

decimal

I

I

I

E

E

U

U

U

U

I

I

I

I

I

numeric

I

I

I

E

E

U

U

U

U

I

I

I

I

I

float

I

I

I

E

E

U

U

U

U

I

I

I

I

I

real

I

I

I

E

E

U

U

U

U

I

I

I

I

I

money

I

I

I

I

I

U

U

U

U

I

I

I

I

I

smallmoney

I

I

I

I

I

U

U

U

U

I

I

I

I

I

text

U

U

U

E

E

U

U

U

U

U

U

U

U

U

unitext

E

E

E

E

E

U

U

U

U

U

U

U

U

U

image

E

E

U

U

U

U

U

U

U

U

U

U

U

U

unichar

I

I

E

I

I

I

I

I

I

E

E

E

E

E

univarchar

I

I

E

I

I

I

I

I

I

E

E

E

E

E

date

I

I

U

I

I

I

U

I

U

U

U

U

U

U

time

I

I

U

I

I

I

U

I

I

U

U

U

U

U

Table 16-4: Explicit, implicit, and unsupported datatype conversions

From

bigint

unsigned bigint

decimal

numeric

float

real

money

smallmoney

text

unitext

image

unichar

univarchar

date

time

binary

I

I

I

I

I

I

I

I

U

I

I

I

I

I

I

varbinary

I

I

I

I

I

I

I

I

U

I

I

I

I

I

I

bit

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

[n]char

E

E

E

E

E

E

E

E

I

I

I

I

I

I

I

[n]varchar

E

E

E

E

E

E

E

E

I

I

I

I

I

I

I

datetime

U

U

U

U

U

U

U

U

U

U

U

I

I

I

I

smalldatetime

U

U

U

U

U

U

U

U

U

U

U

I

I

I

I

bigdatetime

U

U

U

U

U

U

U

U

U

U

U

I

I

I

I

bigtime

U

U

U

U

U

U

U

U

U

U

U

I

I

U

I

tinyint

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

smallint

I

I

I

I

I

I

I

I

U

U

U

U

E

U

U

unsigned smallint

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

int

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

unsigned int

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

bigint

I

I

I

I

I

I

I

U

U

U

E

E

U

U

unsigned bigint

I

I

I

I

I

I

I

U

U

U

E

E

U

U

decimal

I

I

I

I

I

I

I

U

U

U

E

E

U

U

numeric

I

I

I

I

I

I

I

U

U

U

E

E

U

U

float

I

I

I

I

I

I

I

U

U

U

E

E

U

U

real

I

I

I

I

I

I

I

U

U

U

E

E

U

U

money

I

I

I

I

I

I

I

U

U

U

E

E

U

U

smallmoney

I

I

I

I

I

I

I

U

U

U

E

E

U

U

text

U

U

U

U

U

U

U

U

I

U

E

E

U

U

unitext

U

U

U

U

U

U

U

U

I

I

U

U

U

U

image

U

U

U

U

U

U

U

U

U

I

E

E

U

U

unichar

E

E

E

E

E

E

E

E

I

I

I

I

I

I

univarchar

E

E

E

E

E

E

E

E

I

I

I

I

I

I

date

U

U

U

U

U

U

U

U

U

U

U

I

I

I

time

U

U

U

U

U

U

U

U

U

U

U

I

I

I