Adding and Subtracting Date and Time Data Types

Go Up to Working with Dates and Times

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).

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

Advance To:

• Comparing Dates and Times