Type conversions happen automatically, or they can be explicitly requested using the CAST or CONVERT function.
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 conversion:
DATE( expression )— Converts the expression into a date, and removes any hours, minutes or seconds. Conversion errors may be reported.
DATETIME( expression )— Converts the expression into a timestamp. Conversion errors may be reported.
STRING( expression )— Similar
to CAST(value AS CHAR), except
that string(NULL) is the empty
string (''), while 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 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 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, one digit means tenths, two digits mean hundredths, and three 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; IQ supports a zero or a blank. For details on how to load such data from Adaptive Server Enterprise into IQ, see Chapter 7, “Moving Data In and Out of Databases” in Sybase IQ System Administration Guide.
