Datetime expressions

A datetime expression resolves to a value of type DATE, INT, INTERVAL, TIME, or TIMESTAMP, depending on the context. A datetime expression must include one of these:

The next table summarizes the types of arithmetic operations that are valid in a datetime expression. As shown, a datetime expression may include a numeric expression that returns a number, but only in a subset of cases.

Arithmetic operations in a datetime expression
Type of Operand 1 Operator Type of Operand 2 Type of Result Comments
DATE - DATE INT  
DATE +/- NUMBER DATE  
NUMBER + DATE DATE  
         
TIME STAMP - TIMESTAMP INTERVAL INTERVAL(dd, ss) unless Operand 1 and Operand 2 are both any of the following:
  • TIMESTAMP(yyyy)
  • TIMESTAMP(yyyyMM)
  • TIMESTAMP(MM)

In those three cases, the result is INTERVAL(yyyyMM)

DATE - TIMESTAMP INTERVAL INTERVAL(ddssmmffffff)
TIME STAMP - DATE INTERVAL INTERVAL(ddHHmmssffffff)
         
TIME STAMP +/- INTERVAL TIMESTAMP  
INTERVAL + TIMESTAMP TIMESTAMP  
         
DATE +/- INTERVAL TIMESTAMP  
INTERVAL + DATE TIMESTAMP  
         
INTERVAL +/- INTERVAL INTERVAL Operand1 and Operand2 must both have (at most) years and months or both must have (at most) days and a time value
INTERVAL *// NUMBER INTERVAL  

Related reference
Assignments
dateValue()
extend()
intervalValue()
timeValue()
timestampValue()
Expressions
Logical expressions
Numeric expressions
Operators and precedence
Primitive types
Text expressions

Substrings

Feedback
(C) Copyright IBM Corporation 2000, 2005. All Rights Reserved.