Mark, that is exactly right. The difference between to dates is always a number, in this case the number of whole days because of the TRUNC. And the difference can be negative:
SQL> SELECT TRUNC(SYSDATE) - TRUNC(SYSDATE+1) FROM DUAL;
Ric Van Dyke Hotsos Enterprises Cell 248-705-0624 -- ---- ---- ---- ----- Hotsos Symposium March 4-8, 2007. Be there. -- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Bobak, Mark Sent: Thursday, April 13, 2006 10:40 AM To: Rich.Jesse@(protected); oracle-l@(protected) Subject: RE: I was told there would be no (date) math
Well, the error message is a bit odd, but, it might make sense that the statement gets an inconsistent datatypes error.
If your statement: Select trunc(sysdate) - trunc(sysdate) - trunc(sysdate) from dual;
Is processed as: Select (trunc(sysdate) - trunc(sysdate)) - trunc(sysdate) from dual;
Then it ought to raise an inconsistent datatypes error, cause you can't subtract a date from a number.
Try it this way: Select trunc(sysdate) - (trunc(sysdate) - trunc(sysdate)) from dual;
to avoid the error.
But, I agree the error message you see doesn't make sense.
-Mark
-- Mark J. Bobak Senior Oracle Architect ProQuest Information & Learning
For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Jesse, Rich Sent: Thursday, April 13, 2006 9:52 AM To: oracle-l@(protected) Subject: I was told there would be no (date) math
Hey all,
While debugging an analytical function issue using 9.2.0.5, I run this idiotic query:
SELECT TRUNC(SYSDATE) - TRUNC(SYSDATE) - TRUNC(SYSDATE) FROM DUAL;
