Datetime expressions

A datetime expression resolves to a time value (date, interval, time, or timestamp, depending upon the context used). Expressions are specified in various situations; for example, on the right side of an assignment statement. A datetime expression may be composed of any of these:

• An item (or system variable) that contains a date, interval, time or timestamp.

• A datetime literal, which expresses itself as either date, interval, time or timestamp. When used in a datetime expression, a datetime literal must have a type specification to identify the literal type. The functions for datetime literals are:
  • dateValue

  • intervalValue

  • timeValue

  • timeStampValue

  • extendTimeStampValue

• A function invocation that returns an integer or time value such as date, interval, time or timestamp.

The following rules apply for datetime arithmetic calculations, some of which are summarized in the table below:

• The difference between two DATES is an integer.

• An integer added to a DATE or an integer subtracted from a DATE, results in a DATE.

• The difference between two TIMESTAMPS is an INTERVAL.

• An interval added to a timestamp or an interval subtracted from a timestamp, results in a timestamp.

• A date can be assigned to a timestamp; and timestamp can be assigned a date.

• To create an exact number of days, use date arithmetic.

• To preserve the relative day within the month, use timestamp arithmetic.

• Dates can be compared to dates; times can be compared to times; and timestamps can be compared to timestamps; intervals can be compared to intervals.

• Greater than (>) is defined as "follows in time".

• Less than (<) is defined as "precedes in time".

The following table summarizes the types of arithmetic operations that can be performed upon dates, intervals and timestamps:

Operand 1	Operator	Operand 2	Result	Comments
DATE	-	DATE	NUMBER
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 which case 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 be compatible types.
INTERVAL	*//	NUMBER	INTERVAL

Related reference
Assignments
Expressions
Items
Logical expressions
Numeric expressions
Operators and precedence
Primitive types
sysLib.dateValue
sysLib.extendTimeStampValue
sysLib.intervalValue
sysLib.timeValue
sysLib.timeStampValue
Text expressions
Text formatting (system words)
Substrings