HPlogo ALLBASE/SQL Reference Manual > Chapter 7 Data Types

Date/Time Operations

MPE documents

Complete PDF
Table of Contents
Index

E0300 Edition 9 ♥
E0399 Edition 8
E0897 Edition 7

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 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:

Table 7-5 Arithmetic Operations on Date/Time Data Types

Operanda Operator Operand b Result Type
DATE+,-INTERVALDATE
INTERVAL+DATEDATE
DATE-DATEINTERVAL
TIME+,-INTERVALTIME
INTERVAL+TIMETIME
TIME-TIMEINTERVAL
DATETIME+,-INTERVALDATETIME
INTERVAL+DATETIMEDATETIME
DATETIME-DATETIMEINTERVAL
INTERVAL+,-INTERVALINTERVAL
INTERVAL*, /INTEGERINTERVAL
STRING[1] -DATEINTERVAL
STRING[2] + DATEDATE
DATE -STRINGaINTERVAL
DATE + STRINGb DATE
STRING[3] -DATETIMEINTERVAL
DATETIME-STRINGc INTERVAL
STRINGb +DATETIMEDATETIME
DATETIME+STRING DATETIME
STRING[4] -TIMEINTERVAL
STRINGb + TIME TIME
TIME-STRINGd INTERVAL
TIME + STRINGd TIME
STRINGb +,-INTERVALINTERVAL
INTERVAL+,-STRINGb 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.



Decimal Operations

Binary Operations