The following sections explain the rules that apply when values are assigned in SQL statements to database columns or to host variables.
Datetime Assignment Rules
If a string value assigned to a fixed-length or variable-length character column is longer than the defined length of the column (except for trailing spaces), the assignment will fail and an error is returned.
If a string value assigned to a fixed-length character column is shorter than the defined length of the column, the content of the column is padded to the right with blanks after the assignment.
If a string value assigned to a variable-length character column is shorter than the defined maximum length of the column, no blank padding occurs.
Character (both fixed length and variable length) column values assigned to fixed-length host variables in SQL statements are padded with blanks to the right if necessary. Column values assigned to host variables are truncated if they are longer than the declared length of the variable, and a warning is issued.
The following table summarizes the rules for character string assignment:
To column Error if non-blank character would be truncated. Pad right with blanks for fixed-length columns. No blank padding for variable length columns. To variable Truncate and warn. Pad right with blanks.
Numbers assigned to columns or host variables assume the data type of the item to which they are assigned, regardless of the data type of the source.
Integral parts of numbers are never truncated. Fractional parts of decimal and floating point numbers may be truncated if required. No precision is lost when converting integer values to decimal, but this may happen when converting integer values to floating point.
When decimal or floating point values are converted to integers, the fractional part of the number is truncated (not rounded). Note that the range of numbers represented by integers is smaller than the range represented by floating point numbers. Assignment of a floating point number to an integer produces an overflow error if the source number is too large.
In assigning decimal values to decimal targets, the length of the integer part of the source (i.e. the difference between the precision and scale) may not exceed the precision of the target. The necessary number of leading zeros is appended or eliminated, and trailing zeros are added to or digits truncated from the fractional part as required.
Note: Truncation effects can be avoided by explicitly using the ROUND function, see The ROUND Function.
In converting decimal values to floating point, the mantissa of the target is treated as a decimal number with the same precision as the source (for example, 1234.56 becomes 1.23456E3).
In converting floating point values to decimal, digits are truncated from the fractional part of the result as required by the scale of the target. An overflow error occurs if the precision of the target cannot accommodate the integral part of the result.
The following table illustrates the main features of numerical assignments.
Leading zeros are shown where appropriate to indicate the maximum number of digits available. Leading zeros in numerical data are not normally displayed on output.
Datetime Assignment Rules
The following compatibility rules apply when assigning DATETIME values to one another:
- If the value to be assigned is a DATE, the target must also be a DATE.
- If the value to be assigned is a TIME, the target must also be a TIME.
- If the value to be assigned is a TIMESTAMP, the target must also be a TIMESTAMP.
- The CAST function can be used in order to cross-assign.
Interval Assignment Rules
- The following compatibility rules apply when assigning INTERVAL values to one another:
- When assigning a non-null value to an INTERVAL column, the leading precision of the target must be sufficient to represent the value.
- All YEAR-MONTH INTERVAL values are compatible with one another.
- All DAY-TIME INTERVAL values are compatible with one another.
This section summarizes standard compliance concerning assignments.
YES Fully compliant.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40