Converting to elementary value
Practically, there exist two essential elementary data types: string and number. Considering all numeric types, bit, bool, Date, Time and enumerated values as numbers, remaining types (except BLOB) can be considered as string types. DateTime values are not considered as elementary value.
String to number conversion is supported as well as number to string conversion. Converting data may result in data truncation or data loss. In order to check compatibility, corresponding functions might be called explicitly from within the application.
When the target value has a restricted value domain (enumerated type value, date, time), invalid values will cause an error or throw an exception. Conversion errors are always signaled, when trying to convert a BLOB value in anything else or reverse.
Converting strings into strings or numbers depends on the data type for the target value. In most cases, string conversion will succeed, but the result might be truncated in the one or the other way.
Converting strings into numbers accepts also strings not containing valid numbers. Strings will be converted into numbers as far as possible. Thus, a string value "abc" results in an empty value (0), when being converted, but does not cause an error or throws an exception. Similar, a string value "12abc45" would result in 12 ignoring following "abc45" characters.
Converting string values to string values will truncate the right operand (source), when the size of the target is smaller than the source size, e.g. assigning a string value "abcde" to a three character string will result in "abc".
When the target string size is larger than the source, the target will be padded with spaces (' ') or 0 depending on the string type (0-terminated or buffer).
Converting a string to a boolean value checks, whether the string content corresponds to a true value or not. When the string has one of the following values, it is considered as true, otherwise as false:
true, t, yes, y, 1
These values are not case sensitive. The string may contain trailing blanks, but no other trailing characters. Also, no spaces must precede the value.
'TRUE' --> true
'TRU' --> false
'TRUE ' --> true
' TRUE' --> false
Converting a string into an enumeration, the string value passed in the right operand (source) must match exactly (case sensitive comparison) the name of an enumerator defined in the enumeration assigned to the target value type. When no match could be found, conversion results in an error or throws an exception.
Converting string to date requires a valid date string. Valid date strings consist of year (2 or 4 characters), month (1 or 2 characters) and day (1 or 2 characters). Different orders of year (Y), month (M) and day (D) are supported:
d.m.y (German)
m/d/y (English)
y-m-d (default)
The separator chosen determines the position of year, month and day data in the string. When the values passed between separators do not correspond to valid day, month or year numbers, the conversion results in an empty date value.
Converting string to time requires a valid time string. Valid time strings consist of hours, minutes, seconds and hundredth seconds. One string format is supported for time values:
hours [ :minutes [ :seconds [ ,hseconds ] ] ]
Minutes, seconds and hundredth seconds are optional. When the values passed between separators do not correspond to valid values, the conversion results in an empty time value.
There are no problems converting any type of numbers to string values. When the size of the string value is to small for storing the required value, it will be truncated. Also, most number to number conversions will perform well, but may result in truncation.
Converting a numerical value into number returns the numerical value of the source (right) operand. date and time result in an integer value (number of days or hundredth seconds). Precision will be adjusted to the format of the target value. When the target value size is exceeded, the maximum or minimum value will be returned. Thus, assigning a numerical value 1234.56 to an INT(3,1) value will result in 999.9.
Converting an enumerated type value into a number will return the assigned code value for the enumerator.
Converting a numerical value to string returns the corresponding string representation including decimal points and thousands separator according to the target value definition. When the value exceeds the string size, the string is filled with *, but no error is signaled.
Boolean values are converted into Y and N. Enumerated values (code) are converted into enumerator names, when the numerical value passed is a valid code in the enumeration.
Date and time values are returned as yy-mm-dd or hh:mm:ss,hs strings. In order to provide other formats, special date/time formats might be set.
Converting a number to a boolean value results in false, when the value is 0 and in true otherwise. Date and time values result in false, when they are empty and in true otherwise.
Converting a number into an enumerated type value will check the value before assigning it. When the value is not a valid value in the enumeration, conversion signals an error.
Converting a numerical value into a date value assigns the number to the date value, which is interpreted as number of days since January 1st 1870. When the value is less than 0, conversion signals an error.
Converting a numerical value into a time value assigns the number to the time value, which is interpreted as number of hundredth seconds. When the value is less than -1 (empty time value), conversion signals an error.