Explicit Data Conversions in IQ

When you use the INSERT statement to insert data directly from a database rather than from a flat file, you cannot use the load conversion options.

If the data requires explicit conversion, you must use one of the conversion functions, CAST or CONVERT, in the SELECT statement or VALUES list where you specify the data to be inserted. If the data is converted implicitly, SAP Sybase IQ handles the conversion automatically.

An implicit or explicit conversion is required whenever data types in a SELECT statement need to match, but do not. This occurs when you execute an INSERT SELECT from one data type to another, but it also occurs whenever you compare or compute values for differing data types.

These conversions apply to data within a SAP Sybase IQ database, or coming from an SQL Anywhere database, or any other database that is connected as a specialty data store.

Conversions (implicit (I), explicit (E), and unsupported (U) conversions) when there is no WHERE clause in the SELECT statement, or when the WHERE clause is based on a comparison operation (=, > or <) include:

 

To:

From:

ti

si

in

ui

bi

ub

nu

rl

dl

bt

dt

tm

ts

ch

vc

bn

vb

tinyint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

smallint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

int

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

unsigned int

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

bigint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

unsigned bigint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

numeric

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

U

U

real

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

U

U

double

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

U

U

bit

I

I

I

I

I

I

I

I

I

I

U

U

U

I

I

I

I

date

E

E

E

E

E

E

E

E

E

U

I

U

I

E

E

U

U

time

E

E

E

E

E

E

E

E

E

U

U

I

E

E

E

U

U

time-stamp

E

E

E

E

E

E

E

E

E

U

E

I

I

E

E

U

U

char

E

E

E

E

E

E

E

E

E

I

E

E

E

I

I

I

I

varchar

E

E

E

E

E

E

E

E

E

I

E

E

E

I

I

I

I

binary

I

I

I

I

I

I

U

U

U

U

U

U

U

I

I

I

I

varbinary

I

I

I

I

I

I

U

U

U

U

U

U

U

I

I

I

I

The descriptions of the codes used in the tables include:

Code

Data type

Code

Data type

Code

Data type

ti

tinyint

nu

numeric

ts

timestamp

si

smallint

rl

real

ch

char

in

int

dl

double

vc

varchar

ui

unsigned int

bt

bit

bn

binary

bi

bigint

dt

date

vb

varbinary

ub

unsigned bigint

tm

time

Conversions when the WHERE clause in a SELECT statement is based on an arithmetic operation (+, –, and so on) include:

IQ conversions for arithmetic operations
 

To:

From:

ti

si

in

ui

bi

ub

nu

rl

dl

bt

dt

tm

ts

ch

vc

bn

vb

tinyint

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

I

I

smallint

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

I

I

int

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

I

I

unsigned int

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

I

I

bigint

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

I

I

unsigned bigint

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

I

I

numeric

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

real

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

double

I

I

I

I

I

I

I

I

I

I

U

U

U

E

E

U

U

bit

I

I

I

I

I

I

I

I

I

I

U

U

U

I

I

I

I

date

U

U

U

U

U

U

U

U

U

U

U

I

U

U

U

U

U

time

U

U

U

U

U

U

U

U

U

U

I

U

U

U

U

U

U

timestamp

U

U

U

U

U

U

U

U

U

U

U

U

U

U

U

U

U

char

E

E

E

E

E

E

E

E

E

I

U

U

U

I

I

I

I

varchar

E

E

E

E

E

E

E

E

E

I

U

U

U

I

I

I

I

binary

I

I

I

I

I

I

U

U

U

U

U

U

U

I

I

I

I

varbinary

I

I

I

I

I

I

U

U

U

U

U

U

U

I

I

I

I
Note: In arithmetic operations, bit data is implicitly converted to tinyint.

Conversions for the INSERT and UPDATE statements include:

 

To:

From:

ti

si

in

ui

bi

ub

nu

rl

dl

bt

dt

tm

ts

ch

vc

bn

vb

tinyint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

smallint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

int

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

unsigned int

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

bigint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

unsigned bigint

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

I

I

numeric

I

I

I

I

I

I

I

I

I

E

E

E

E

E

E

U

U

real

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

U

U

double

I

I

I

I

I

I

I

I

I

I

E

E

E

E

E

U

U

bit

I

I

I

I

I

I

I

I

I

I

U

U

U

I

I

I

I

date

E

E

E

E

E

E

E

E

E

E

I

U

I

E

E

U

U

time

E

E

E

E

E

E

E

E

E

E

U

I

E

E

E

U

U

time-stamp

E

E

E

E

E

E

E

E

E

E

E

I

I

E

E

U

U

char

I

I

I

I

I

I

I

I

I

I

E

E

E

I

I

I

I

varchar

I

I

I

I

I

I

I

I

I

I

E

E

E

I

I

I

I

binary

I

I

I

I

I

I

U

U

U

I

U

U

U

I

I

I

I

varbinary

I

I

I

I

I

I

U

U

U

I

U

U

U

I

I

I

I
Parent topic: Load Conversion Options