HP 3000 Manuals HP 3000 Manuals
Date/Time Operations DATE, TIME, DATETIME, or INTERVAL values may only be assigned to a column with a matching data type or to a fixed or variable length character string column or host variable. Otherwise an error condition is generated. All rules regarding assignment to a character string are also true for date/time assignment to a character string variable or column. Conversions of the individual fields of a date/time data type follow the rules given earlier in this subsection for the corresponding data type.
NOTE The validity of dates prior to 1753 (transition of Julian to Gregorian calendar) cannot be guaranteed.
DATE, TIME, DATETIME, and INTERVAL data types behave similar to character strings in data manipulation statements. The examples below illustrate this. Examples INSERT DATETIME, DATE, TIME and INTERVAL values: INSERT INTO ManufDB.TestData (BatchStamp, TestDate, TestStart, TestEnd, LabTime, PassQty, TestQty) VALUES ('1984-08-19 08:45:33.123', '1984-08-23', '08:12:19', '13:23:01', '5 10:35:15.700', 49, 50) SELECT DATE and TIME values: SELECT TestDate, TestStart FROM ManufDB.TestData WHERE TestDate = '1984-08-23' DATETIME and INTERVAL values: SELECT BatchStamp, LabTime FROM ManufDB.TestData WHERE TestDate = '1984-08-23' UPDATE DATE and TIME values: UPDATE ManufDB.TestData SET TestDate = '1984-08-25', TestEnd = '19:30:00' WHERE BatchStamp = '1984-08-19 08:45:33.123' INTERVAL values: UPDATE ManufDB.TestData SET LabTime = '5 04:23:00.000' WHERE TestEnd = '19:30:00' Note that the radix of DATE and TIME data is seconds, whereas the radix of DATETIME and INTERVAL data is milliseconds. Date/time data types can also be converted to formats other than the default formats by the date/time functions described in the "Expressions" chapter. Use of Date/Time Data Types in Arithmetic Expressions You can use a variety of operations to increment, decrement, add or subtract date, time, datetime, and interval values. The following table shows the valid operations and the data type of the result: Table 7-5. Arithmetic Operations on Date/Time Data Types ------------------------------------------------------------------------------------------------- | | | | | | Operand 1 | Operator | Operand 2 | Result Type | | | | | | ------------------------------------------------------------------------------------------------- | | | | | | DATE | +,- | INTERVAL | DATE | | | | | | | INTERVAL | + | DATE | DATE | | | | | | | DATE | - | DATE | INTERVAL | | | | | | | TIME | +,- | INTERVAL | TIME | | | | | | | INTERVAL | + | TIME | TIME | | | | | | | TIME | - | TIME | INTERVAL | | | | | | | DATETIME | +,- | INTERVAL | DATETIME | | | | | | | INTERVAL | + | DATETIME | DATETIME | | | | | | | DATETIME | - | DATETIME | INTERVAL | | | | | | | INTERVAL | +,- | INTERVAL | INTERVAL | | | | | | | INTERVAL | *, / | INTEGER | INTERVAL[REV BEG] | | | | | | | STRING (1) | - | DATE | INTERVAL | | | | | | | STRING (2) | + | DATE | DATE | | | | | | | DATE | - | STRING (1) | INTERVAL | | | | | | | DATE | + | STRING (2) | DATE | | | | | | | STRING (3) | - | DATETIME | INTERVAL | | | | | | | DATETIME | - | STRING (3) | INTERVAL | | | | | | | STRING (2) | + | DATETIME | DATETIME | | | | | | | DATETIME | + | STRING (2) | DATETIME | | | | | | | STRING (4) | - | TIME | INTERVAL | | | | | | | STRING (2) | + | TIME | TIME | | | | | | | TIME | - | STRING (4) | INTERVAL | | | | | | | TIME | + | STRING (4) | TIME | | | | | | | STRING (2) | +,- | INTERVAL | INTERVAL | | | | | | | INTERVAL | +,- | STRING (2) | INTERVAL[REV END] | | | | | | ------------------------------------------------------------------------------------------------- (1) The format for string should be DATE. (2) The format for string should be INTERVAL. (3) The format for string should be DATETIME. (4) The format for string should be TIME. 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.[REV BEG] 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.[REV END] 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' Date/Time Data Types and Aggregate Functions You can use the aggregate functions MIN, MAX, and COUNT in queries on columns of type DATE, TIME, DATETIME, and INTERVAL. SUM and AVG can be done on INTERVAL data types only.