Converting data types explicitly

UltraLite allows you to request data type conversions explicitly, by using either the CAST or CONVERT function.

NOTE

Self-casting usually has no effect on operations. However, self-casts to CHAR/VARCHAR, BINARY/VARBINARY and NUMERIC are not no-op procedures.

You can CAST or CONVERT most combinations of data types, as illustrated by the table that follows.

The ability to convert or not is contingent upon the value used in the conversion. As the Value-dependent column shows, the value must be compatible with the new data type to avoid generating a specific type of conversion error. For example:

  • If you cast varchar "1234" to long, this conversion is supported. However, if you cast varchar "hello" to long, then this conversion generates a SQLE_CONVERSION_ERROR error because hello is not a number.

  • If you cast long 1234 to short, this conversion is supported. However, if you cast long 1000000 to short, then this conversion generates a SQLE_OVERFLOW_ERROR error, because 1000000 is beyond the range of numbers a short can hold.

From Always Never Value-dependent
BINARY or VARBINARY

CHAR or VARCHAR

BINARY

LONG BINARY

BIT

TINYINT

SHORT INT

SIGNED SHORT

BIGINT

SIGNED BIG

LONG VARCHAR

REAL

TIME

TIMESTAMP

DOUBLE

DATE

NUMERIC

UID1

LONG BINARY

BINARY

LONG BINARY

BIT

CHAR or VARCHAR

LONG VARCHAR

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

REAL

DOUBLE

NUMERIC

DATE

TIME

TIMESTAMP

UID

N/A
BIT

CHAR or VARCHAR

BINARY

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

REAL

SIGNED BIG

DOUBLE

NUMERIC

LONG VARCHAR

LONG BINARY

DATE

TIME

TIMESTAMP

UID

N/A
CHAR or VARCHAR

BINARY or VARBINARY

CHAR or VARCHAR

LONG VARCHAR

LONG BINARY

BIT

TINYINT

SIGNED SHORT

SHORT INT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

DOUBLE

NUMERIC

REAL

DATE

TIME

TIMESTAMP

UID

LONG VARCHAR

CHAR or VARCHAR

LONG VARCHAR

BINARY or VARBINARY

LONG BINARY

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

REAL

NUMERIC

DATE

TIME

TIMESTAMP

DOUBLE

UID

TINYINT

BINARY or VARBINARY

CHAR or VARCHAR

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

REAL

DOUBLE

NUMERIC

LONG VARCHAR

LONG BINARY

DATE

TIME

TIMESTAMP

UID

SHORT INT

BINARY or VARBINARY

CHAR or VARCHAR

SHORT INT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

REAL

DOUBLE

NUMERIC

LONG VARCHAR

LONG BINARY

DATE

TIME

TIMESTAMP

UID

BIT

TINYINT

SIGNED SHORT

SIGNED SHORT

BINARY or VARBINARY

CHAR or VARCHAR

SIGNED SHORT

SIGNED LONG

SIGNED BIG

REAL

DOUBLE

NUMERIC

LONG VARCHAR

LONG BINARY

DATE

TIME

TIMESTAMP

UID

SHORT INT

LONG INT

BIGINT

BIT

TINYINT

LONG INT

BINARY or VARBINARY

CHAR or VARCHAR

LONG INT

BIGINT

SIGNED BIG

REAL

DOUBLE

NUMERIC

LONG VARCHAR

LONG BINARY

DATE

TIME

TIMESTAMP

UID

BIT

TINYINT

SHORT INT

SIGNED SHORT

SIGNED LONG

SIGNED LONG

BINARY or VARBINARY

CHAR or VARCHAR

SIGNED LONG

SIGNED BIG

REAL

DOUBLE

NUMERIC

DATE

TIMESTAMP

LONG VARCHAR

LONG BINARY

TIME

UID

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

BIGINT

BIGINT

BINARY or VARBINARY

CHAR or VARCHAR

BIGINT

REAL

DOUBLE

NUMERIC

LONG VARCHAR

LONG BINARY

DATE

TIME

TIMESTAMP

UID

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

SIGNED BIG

SIGNED BIG

BINARY or VARBINARY

CHAR or VARCHAR

SIGNED BIG

REAL

DOUBLE

NUMERIC

DATE

TIMESTAMP

LONG VARCHAR

LONG BINARY

TIME

UID

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

REAL

CHAR or VARCHAR

REAL

DOUBLE

NUMERIC

LONG VARCHAR

BINARY or VARBINARY

LONG BINARY

DATE

TIME

TIMESTAMP

UID

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

DOUBLE

CHAR or VARCHAR

DOUBLE

NUMERIC

LONG VARCHAR

BINARY or VARBINARY

LONG BINARY

DATE

TIME

TIMESTAMP

UID

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

REAL

NUMERIC

CHAR or VARCHAR

REAL

NUMERIC

DOUBLE

LONG VARCHAR

LONG BINARY

DATE

TIME

TIMESTAMP

UID

BINARY or VARBINARY2

BIT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

TINYINT

DATE

CHAR or VARCHAR

SIGNED LONG

SIGNED BIG

DATE

TIMESTAMP

LONG VARCHAR

LONG BINARY

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

BIGINT

REAL

DOUBLE

NUMERIC

TIME

BINARY or VARBINARY

UID

TIME

CHAR or VARCHAR

TIME

TIMESTAMP

LONG VARCHAR

LONG BINARY

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

REAL

DOUBLE

NUMERIC

DATE

BINARY or VARBINARY

UID

TIMESTAMP

CHAR or VARCHAR

SIGNED LONG

SIGNED BIG

DATE

TIME

TIMESTAMP

LONG VARCHAR

LONG BINARY

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

BIGINT

REAL

DOUBLE

NUMERIC

BINARY or VARBINARY

UID

UID

CHAR or VARCHAR

UID

LONG VARCHAR

LONG BINARY

BIT

TINYINT

SHORT INT

SIGNED SHORT

LONG INT

SIGNED LONG

BIGINT

SIGNED BIG

REAL

DOUBLE

NUMERIC

DATE

TIME

TIMESTAMP

BINARY or VARBINARY1

1 The BINARY value must be a 16 byte length to be compatible with a UUID.

2 Only works if the source NUMERIC value is able to cast as a BIGINT.