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
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
^[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. 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.

Date/Time Operations

Technical documentation

» Table of Contents

» Index

Examples

Use of Date/Time Data Types in Arithmetic Expressions

Use of Date/Time Data Types in Predicates

Date/Time Data Types and Aggregate Functions