Reference Manual
   CHAPTER 7. SQL Data Types     

Data type conversions

Type conversions can happen automatically, or they can be explicitly requested using the CAST or CONVERT function.

If a string is used in a numeric expression or as an argument to a function that expects a numeric argument, the string is converted to a number.

If a number is used in a string expression or as a string function argument, it is converted to a string before being used.

All date constants are specified as strings. The string is automatically converted to a date before use.

There are certain cases where the automatic database conversions are not appropriate.

'12/31/90' + 5 - Adaptive Server Anywhere tries to convert the string to a number
'a' > 0 -- Adaptive Server Anywhere tries to convert 'a' to a number

The CAST or CONVERT functions can be used to force type conversions. For information about the CAST and CONVERT functions, see Data type conversion functions.

The following functions can also be used to force type conversions (see SQL Functions).

• DATE( value )     Converts the expression into a date, and removes any hours, minutes or seconds. Conversion errors may be reported.

• STRING( value )     Similar to CAST( value AS CHAR ), except that string( NULL ) is the empty string (''), while CAST( NULL AS CHAR ) is the NULL value.

• VALUE+0.0     Equivalent to CAST( value AS DECIMAL ).

  Compatibility of string-to-date/time conversions

There are some differences in behavior between Adaptive Server Anywhere 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, Adaptive Server Enterprise uses a default date of January 1, 1900, but 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. Adaptive Server Anywhere interprets the value the same way, regardless of the separator.

Example 

• Adaptive Server Enterprise would convert 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

  Adaptive Server Anywhere converts the milliseconds value in the manner that 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