Type conversions happen automatically, or you can explicitly request them using the CAST or CONVERT function.
If a string is used in a numeric expression or as an argument to a function expecting a numeric argument, the string is converted to a number before use.
If a number is used in a string expression or as a string function argument, then the number is converted to a string before use.
All date constants are specified as strings. The string is automatically converted to a date before use.
There are certain cases where the automatic data type conversions are not appropriate.
'12/31/90' + 5 -- Tries to convert the string to a number 'a' > 0 -- Tries to convert 'a' to a number
You can use the CAST or CONVERT function to force type conversions.
The following functions can also be used to force type conversions:
DATE( expression ) – converts the expression into a date, and removes any hours, minutes or seconds. Conversion errors might be reported.
DATETIME( expression ) – converts the expression into a timestamp. Conversion errors might be reported.
STRING( expression ) – similar
to CAST(value AS CHAR), except
that string(NULL) is the empty
string (''), whereas CAST(NULL AS CHAR) is the
NULL value.
For information about the CAST and CONVERT functions, see “Data type conversion functions”.
There are some differences in behavior between Sybase IQ and Adaptive Server Enterprise when converting strings to date and time data types.
If a string containing only a time value (no date) is converted to a date/time data type, Sybase IQ and Adaptive Server Enterprise both use a default date of January 1, 1900. Adaptive Server Anywhere uses the current date.
If the milliseconds portion of a time is less than 3 digits, Adaptive Server Enterprise interprets the value differently depending on whether it was preceded by a period or a colon. If preceded by a colon, the value means thousandths of a second. If preceded by a period, 1 digit means tenths, 2 digits mean hundredths, and 3 digits mean thousandths. Sybase IQ and Adaptive Server Anywhere interpret the value the same way, regardless of the separator.
Adaptive Server Enterprise converts the values below as shown.
12:34:56.7 to 12:34:56.700 12.34.56.78 to 12:34:56.780 12:34:56.789 to 12:34:56.789 12:34:56:7 to 12:34:56.007 12.34.56:78 to 12:34:56.078 12:34:56:789 to 12:34:56.789
Sybase IQ converts the milliseconds value in the manner that Adaptive Server Enterprise does for values preceded by a period, in both cases:
12:34:56.7 to 12:34:56.700 12.34.56.78 to 12:34:56.780 12:34:56.789 to 12:34:56.789 12:34:56:7 to 12:34:56.700 12.34.56:78 to 12:34:56.780 12:34:56:789 to 12:34:56.789
For dates in the first 9 days of a month and hours less than 10, Adaptive Server Enterprise supports a blank for the first digit; Sybase IQ supports a zero or a blank. For details on how to load such data from Adaptive Server Enterprise into Sybase IQ, see Chapter 7, “Moving Data In and Out of Databases” in Sybase IQ System Administration Guide.
Sybase IQ supports BIT to BINARY and BIT to VARBINARY implicit and explicit conversion and is compatible with ASE support of these conversions. Sybase IQ implicitly converts BIT to BINARY and BIT to VARBINARY data types for comparison operators, arithmetic operations, and INSERT and UPDATE statements.
For BIT to BINARY conversion, bit value ‘b’ is copied to the first byte of the binary string and the rest of the bytes are filled with zeros. For example, BIT value ‘1’ is converted to BINARY(n) string ‘0x0100...00 having 2n nibbles. BIT value ‘0’ is converted to BINARY string ‘0x00...00’.
For BIT to VARBINARY conversion, BIT value ‘b’ is copied to the first byte of the BINARY string and the remaining bytes are not used; that is, only one byte is used. For example, BIT value ‘1’ is converted to VARBINARY(n) string ‘0x01’ having 2 nibbles.
The result of both implicit and explicit conversions of BIT to BINARY and BIT to VARBINARY data types is the same. The following table contains examples of BIT to BINARY and VARBINARY conversions.
Conversion of BIT value ‘1’ to |
Result |
|---|---|
BINARY(3) |
0x010000 |
VARBINARY(3) |
0x01 |
BINARY(8) |
0x0100000000000000 |
VARBINARY(8) |
0x01 |
BIT to BINARY and BIT to VARBINARY conversion examples These examples illustrate both implicit and explicit conversion of BIT to BINARY and BIT to VARBINARY data types.
Given the following tables and data:
CREATE TABLE tbin(c1 BINARY(9)) CREATE TABLE tvarbin(c2 VARBINARY(9)) CREATE TABLE tbar(c2 BIT) INSERT tbar VALUES(1) INSERT tbar VALUES(0)
Implicit conversion of BIT to BINARY:
INSERT tbin SELECT c2 FROM tbar c1 --- 0x010000000000000000 (18 nibbles) 0x000000000000000000 (18 nibbles)
Implicit conversion of BIT to VARBINARY:
INSERT tvarbin SELECT c2 FROM tbar c2 --- 0x01 0x00
Explicit conversion of BIT to BINARY:
INSERT tbin SELECT CONVERT (BINARY(9), c2) FROM tbar c1 --- 0x010000000000000000 (18 nibbles) 0x000000000000000000 (18 nibbles)
Explicit conversion of BIT to VARBINARY:
INSERT tvarbin SELECT CONVERT(VARBINARY(9), c2) FROM tbar c2 --- 0x01 0x00
