Date/Time Operations

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 Chapter 8 “Expressions”

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. Table 7-5 “Arithmetic Operations on Date/Time Data Types” shows the valid operations and the data type of the result:

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'

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.