Neoview SQL Reference Manual (R2.4)

• Any aggregate functions, sequence functions, scalar subqueries, CASE expressions, or CAST
expressions that return datetime or interval values
• OLAP window functions
Considerations for Datetime Value Expressions
Data Type of Result
In general, the data type of the result is the data type of the datetime-primary part of the
datetime expression. For example, datetime value expressions include:
The sum of the current date and an interval value of one
day.
CURRENT_DATE + INTERVAL '1' DAY
The sum of the current date and the interval value in
column EST_COMPLETE.
CURRENT_DATE + est_complete
The sum of the ship timestamp for the specified project
and an interval value of seven days, four hours.
( SELECT ship_timestamp FROM project WHERE
projcode=1000 ) + INTERVAL '07:04' DAY TO
HOUR
The datetime primary in the first expression is CURRENT_DATE, a function that returns a value
with DATE data type. Therefore, the data type of the result is DATE.
In the last expression, the datetime primary is this scalar subquery:
( SELECT ship_timestamp FROM project WHERE projcode=1000 )
The preceding subquery returns a value with TIMESTAMP data type. Therefore, the data type
of the result is TIMESTAMP.
Restrictions on Operations With Datetime or Interval Operands
You can use datetime and interval operands with arithmetic operators in a datetime value
expression only in these combinations:
Result TypeOperand 2OperatorOperand 1
DatetimeInterval+ or –Datetime
DatetimeDatetime+Interval
When a numeric value is added to or subtracted from a DATE type, the numeric value is
automatically CASTed to an INTERVAL DAY value. When a numeric value is added to or
subtracted from a time type or a timestamp type, the numeric value is automatically CASTed to
an INTERVAL SECOND value. For information on CAST, see “CAST Expression” (page 337).
For more information on INTERVALS, see “Interval Value Expressions” (page 246)
When using these operations, note:
• Adding or subtracting an interval of months to a DATE value results in a value of the same
day plus or minus the specified number of months. Because different months have different
lengths, this is an approximate result.
• Datetime and interval arithmetic can yield unexpected results, depending on how the fields
are used. For example, execution of this expression (evaluated left to right) returns an error:
DATE '2007-01-30' + INTERVAL '1' MONTH + INTERVAL '7' DAY
In contrast, this expression (which adds the same values as the previous expression, but in
a different order) correctly generates the value 2007-03-06:
DATE '2007-01-30' + INTERVAL '7' DAY + INTERVAL '1' MONTH
You can avoid these unexpected results by using the “ADD_MONTHS Function” (page 326).
Expressions 243