If all operands are date/time type, only a subtraction operation is allowed and its return value is BIGINT. Note that the unit of the operation differs depending on the types of the operands. Both addition and subtraction operations are allowed in case of date/time and integer types In this case, operation units and return values are date/time data type.
The following table shows operations allowed for each operand type, and their result types.
Allowable Operation and Result Data Type by Operand Type
|
TIME |
DATE |
TIMESTAMP |
DATETIME |
INT |
---|---|---|---|---|---|
TIME |
A subtraction is allowed. |
X |
X |
X |
An addition and a subtraction are allowed. |
DATE |
X |
A subtraction is allowed. |
A subtraction is allowed. |
A subtraction is allowed. |
An addition and a subtraction are allowed. |
TIMESTAMP |
X |
A subtraction is allowed. |
A subtraction is allowed. |
A subtraction is allowed. |
An addition and a subtraction are allowed. |
DATETIME |
X |
A subtraction is allowed. |
A subtraction is allowed. |
A subtraction is allowed. |
An addition and a subtraction are allowed. |
INT |
An addition and a subtraction are allowed. |
An addition and a subtraction are allowed. |
An addition and a subtraction are allowed. |
An addition and a subtraction are allowed. |
All operations are allowed. |
If any of the date/time arguments contains NULL, NULL is returned.
-- initial systimestamp value
SELECT SYSDATETIME;
SYSDATETIME
===============================
07:09:52.115 PM 01/14/2010
-- time type + 10(seconds) returns time type
SELECT (CAST (SYSDATETIME AS TIME) + 10);
( cast( SYS_DATETIME as time)+10)
====================================
07:10:02 PM
-- date type + 10 (days) returns date type
SELECT (CAST (SYSDATETIME AS DATE) + 10);
( cast( SYS_DATETIME as date)+10)
====================================
01/24/2010
-- timestamp type + 10(seconds) returns timestamp type
SELECT (CAST (SYSDATETIME AS TIMESTAMP) + 10);
( cast( SYS_DATETIME as timestamp)+10)
=========================================
07:10:02 PM 01/14/2010
-- systimestamp type + 10(milliseconds) returns systimestamp type
SELECT (SYSDATETIME + 10);
( SYS_DATETIME +10)
===============================
07:09:52.125 PM 01/14/2010
SELECT DATETIME '09/01/2009 03:30:30.001 pm'- TIMESTAMP '08/31/2009 03:30:30 pm';
datetime '09/01/2009 03:30:30.001 pm'-timestamp '08/31/2009 03:30:30 pm'
=======================================
86400001
SELECT TIMESTAMP '09/01/2009 03:30:30 pm'- TIMESTAMP '08/31/2009 03:30:30 pm';
timestamp '09/01/2009 03:30:30 pm'-timestamp '08/31/2009 03:30:30 pm'
=======================================
86400