ALLBASE/SQL Reference Manual (36216-90216)

Chapter 7 219
Data Types
Date/Time Operations
These arithmetic operations obey the normal rules associated with dates and times. If a
date/time arithmetic operation results in an invalid value (for example, a date prior to
'0000-01-01'), an error is generated. If the format for the string does not match the above
default type, an error is generated. Another solution is to apply TO_DATE, TO_TIME,
TO_DATETIME, and TO_INTERVAL to the string so that the correct format is used.
You can also use the Add Months function to add or subtract from the month portion of the
DATE or DATETIME column. In the result, the day portion is unaffected, only the month and,
if necessary, the year portions are affected. However, if the addition of the month causes an
invalid day (such as 89-02-30), then a warning message is generated and the value is
truncated to the last day of the month.
Use of Date/Time Data Types in Predicates
DATE, TIME, DATETIME, and INTERVAL data types can be used in all predicates except
the LIKE predicate. LIKE works only with CHAR or VARCHAR values and so requires the
use of the TO_CHAR conversion function to be used with a DATETIME column. Items of type
DATE, TIME, DATETIME, and INTERVAL can be compared with items of the same type or
with literals of type CHAR or VARCHAR. All comparisons are chronological, which means
that the point which is farthest from '0000-01-01 00:00:00.000' is the greatest value. String
representations of each data type (in host variables or as literals) can also be compared
following normal string comparison rules. Some examples follow:
SELECT * FROM ManufDB.TestData
WHERE BatchStamp = '1984-06-19 08:45:33.123'
AND TestDate = '1984-06-27'
SELECT * FROM ManufDB.TestData
WHERE Testend - TestStart <= '0 06:00:00.000'
STRING
d
− TIME INTERVAL
STRINGb + TIME TIME
TIME − STRINGd INTERVAL
TIME + STRINGd TIME
STRINGb +,− INTERVAL INTERVAL
INTERVAL +,− STRINGb INTERVAL
a. The format for string should be DATE.
b. The format for string should be INTERVAL.
c. The format for string should be DATETIME.
d. The format for string should be TIME.
Table 7-5. Arithmetic Operations on Date/Time Data Types
Operanda Operator Operand b Result Type