Adding and Subtracting Datetime Data Types

Go Up to DATE, TIME, and TIMESTAMP Data Types

The following table shows the result of adding and subtracting DATE, TIME, TIMESTAMP, and numeric values. “Numeric value” refers to any value that can be cast as an exact numeric value by the database engine (for example, INTEGER, DECIMAL, or NUMERIC).

Adding and Subtracting Date/time Data Types

Operand1	Operator	Operand2	Result
DATE	+	DATE	Error
DATE	+	TIME	TIMESTAMP(concatenation)
DATE	+	TIMESTAMP	Error
DATE	+	Numeric value	DATE + number of days: fractional part ignored
TIME	+	DATE	TIMESTAMP(concatenation)
TIME	+	TIME	Error
TIME	+	TIMESTAMP	Error
TIME	+	Numeric value	TIME + number of seconds: 24-hour modulo arithmetic
TIMESTAMP	+	DATE	Error
TIMESTAMP	+	TIME	Error
TIMESTAMP	+	TIMESTAMP	Error
TIMESTAMP	+	Numeric value	TIMESTAMP: DATE + number of days; TIME + fraction of day converted to seconds
DATE	–	DATE	DECIMAL(9,0) representing the number of days
DATE	–	TIME	Error
DATE	–	TIMESTAMP	Error
DATE	–	Numeric value	DATE: number of days; fractional part ignored
TIME	–	DATE	Error
TIME	–	TIME	DECIMAL(9,4) representing number of seconds
TIME	–	TIMESTAMP	Error
TIME	–	Numeric value	TIME: number of seconds; 24-hour modulo arithmetic
TIMESTAMP	–	DATE	Error
TIMESTAMP	–	TIME	Error
TIMESTAMP	–	TIMESTAMP	DECIMAL(18,9) representing days and fraction of day
TIMESTAMP	–	Numeric value	TIMESTAMP: DATE – number of days; TIME: fraction of day converted to seconds

Note:
Numeric value + DATE, TIME, or TIMESTAMP is symmetric to DATE, TIME, or TIMESTAMP + numeric value.

Advance To:

• Using Date/time Data Types with Aggregate Functions