Hi Tom,
What is the datatype of subtraction of dates? For example, in a query
select ((sysdate+1)-(sysdate-1)) from dual;
The result of the query is 2 days.
The following statement shows that the result is NUMBER. (It creates the table with F1 which is a NUMBER).
create table t1 as select ((sysdate+1)-(sysdate-1)) f1 from dual;
Now.
I can convert the result of first query to Interval Day to Second:
SQL> select ((sysdate+1)-(sysdate-1)) day to second from dual;
((SYSDATE+1)-(SYSDATE-1))DAYTOSECOND
--------------------------------------------------------------
+000000002 00:00:00
But I cannot convert number without using NUMTODSINTERVAL. The following query returns an error:
SQL> select (2) day to second from dual;
select (2) day to second from dual
*
ERROR at line 1:
ORA-30083: syntax error was found in interval value expression
because day to second expects timestamps, so, the dates are cast as timestamps...
and timestamps return intervals
and intervals can be of type day to second.
so, it is not the subtraction of two dates in question, it is the application of a cast that cause the underlying types to be "converted"
ops$tkyte%ORA10GR2> select cast(sysdate+1 as timestamp) - cast(sysdate-1 as timestamp)
2 from dual
3 where ((sysdate+1)-(sysdate-1)) day to second = numtodsinterval(2,'day')
4 /
Execution Plan
----------------------------------------------------------
Plan hash value: 4034615273
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((SYSDATE@!+1-(SYSDATE@!-1))DAY(2) TO
SECOND(6)=INTERVAL'+000000002 00:00:00.000000000' DAY(9) TO SECON
D(9))
you can see the "implicit conversion" happening in the predicate of an explain plan...