Neoview SQL Reference Manual (R2.4 SP2)

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:
Result Data TypeDescriptionDatetime Expression
DATEThe sum of the current date and an
interval value of one day.
CURRENT_DATE + INTERVAL '1'
DAY
DATEThe sum of the current date and the
interval value in column
EST_COMPLETE.
CURRENT_DATE + est_complete
TIMESTAMPThe 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 345).
For more information on INTERVALS, see “Interval Value Expressions” (page 249)
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 334).
246 SQL Language Elements