Category: ASE: Functions: Datatype Conversion Functions

Datatype Conversion Functions

Datatype Conversion Functions transform a datatype into another datatype and set the output format for date and time datatypes.

Usage of Datatype Conversion Functions

Datatype Conversion Functions are used in a Query in a select-Statement wherever so-called "expressions" are allowed as well as in where clauses.

implicit conversions - automatic conversion of datatypes

Adaptive Server® automatically converts datatypes. This is called implicit conversion. For example, char datatypes can be compared to datetime, smallint to int and char-expressions with char expressions of different length. Here ASE automatically converts from one datatype to the other.

explicit conversion - manual conversion of datatypes

A large proportion of datatype conversions have to be explicitly declared. This is called explicit conversions. The datatype conversion functions listed below can be used to do this. So, for example numeric values have to be converted into char prior to concatenating them. The example below demonstrates a concatenation of the numbers 4 and 5; the return value is 45

    select convert(char(1),4) + convert(char(1),5)

    go

    -------

    45

unsupported conversion of datatypes

Individual datatypes cannot be arbitrarily converted into any of the other datatypes. This is valid for implicit as well as explicit conversions.

Compatibility table for ASE datatype conversions among the individual datatypes

Compatibility Table
i = implicit conversion e = explicit conversion u = unsupported conversion - = Conversion possible but not meaningful
	b i n a r y	v a r b i n a r y	b i t	(n) c h a r	(n) v a r c h a r	d a t e t i m e	s m a l l d a t e t i m e	t i n y i n t	s m a l l i n t	u n s i g n e d s m a l l i n t	i n t	u n s i g n e d i n t	b i g i n t	u n s i g n e d b i g i n t	d e c i m a l	n u m e r i c	f l o a t	r e a l	m o n e y	s m a l l m o n e y	t e x t	u n i t e x t	i m a g e	u n i c h a r	u n i v a r c h a r	d a t e	t i m e
binary	-	i	i	i	i	u	u	i	i	i	i	i	i	i	i	i	i	i	i	i	u	i	i	i	i	i	i
varbinary	i	-	i	i	i	u	u	i	i	i	i	i	i	i	i	i	i	i	i	i	u	i	i	i	i	i	i
bit	i	i	-	i	i	u	u	i	i	i	i	i	i	i	i	i	i	i	i	i	u	u	u	e	e	u	u
(n)char	i	i	e	-	i	i	e	e	e	e	e	e	e	e	e	e	e	e	e	e	i	i	i	i	i	i	i
(n)varchar	i	i	e	i	-	i	e	e	e	e	e	e	e	e	e	e	e	e	e	e	i	i	i	i	i	i	i
datetime	i	i	u	i	i	-	i	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	i	i	i	i
smalldatetime	i	i	u	i	i	i	-	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	i	i	i	i
tinyint	i	i	i	e	e	u	u	-	i	i	i	i	i	i	i	i	i	i	i	i	u	u	u	e	e	u	u
smallint	i	i	i	e	e	u	u	i	-	i	i	i	i	i	i	i	i	i	i	i	u	u	u	u	e	u	u
unsigned smallint	i	i	i	e	e	u	u	i	i	-	i	i	i	i	i	i	i	i	i	i	u	u	u	e	e	u	u
int	i	i	i	e	e	u	u	i	i	i	-	i	i	i	i	i	i	i	i	i	u	u	u	e	e	u	u
unsigned int	i	i	i	e	e	u	u	i	i	i	i	-	i	i	i	i	i	i	i	i	u	u	u	e	e	u	u
bigint	i	i	i	e	e	u	u	i	i	i	i	i	-	i	i	i	i	i	i	i	u	u	u	e	e	u	u
unsigned bigint	i	i	i	e	e	u	u	i	i	i	i	i	i	-	i	i	i	i	i	i	u	u	u	e	e	u	u
decimal	i	i	i	e	e	u	u	i	i	i	i	i	i	i	-	i	i	i	i	i	u	u	u	e	e	u	u
numeric	i	i	i	e	e	u	u	i	i	i	i	i	i	i	i	-	i	i	i	i	u	u	u	e	e	u	u
float	i	i	i	e	e	u	u	i	i	i	i	i	i	i	i	i	-	i	i	i	u	u	u	e	e	u	u
real	i	i	i	e	e	u	u	i	i	i	i	i	i	i	i	i	i	-	i	i	u	u	u	e	e	u	u
money	i	i	i	i	i	u	u	i	i	i	i	i	i	i	i	i	i	i	-	i	u	u	u	e	e	u	u
smallmoney	i	i	i	i	i	u	u	i	i	i	i	i	i	i	i	i	i	i	i	-	u	u	u	e	e	u	u
text	u	u	u	e	e	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	-	i	u	e	e	u	u
unitext	e	e	e	e	e	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	i	-	i	u	u	u	u
images	e	e	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	i	-	e	e	u	u
unichar	i	i	e	i	i	i	i	e	e	e	e	e	e	e	e	e	e	e	e	e	i	i	i	-	i	i	i
univarchar	i	i	e	i	i	i	i	e	e	e	e	e	e	e	e	e	e	e	e	e	i	i	i	i	-	i	i
date	i	i	u	i	i	i	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	i	i	-	i
time	i	i	u	i	i	i	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	u	i	i	i	-

List of ASE Datatype Conversion Functions

ASE® provides a number of datatype conversion functions:

ASE T-SQL - Aggregate Functions,
biginttohex(),
bintostr(),
cast(),
convert(),
explicit conversion,
hextobigint(),
hextoint(),
implicit conversions,
inttohex().