RE: I was told there would be no (date) math

From: <oracle-l-bounce_at_freelists.org>
Date: Thu, 13 Apr 2006 12:01:13 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF4EEF1E@WIN02.hotsos.com>

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; TRUNC(SYSDATE)-TRUNC(SYSDATE+1)

---

                             -1

Ric Van Dyke
Hotsos Enterprises
Cell 248-705-0624

---

Hotsos Symposium March 4-8, 2007. Be there.
-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark Sent: Thursday, April 13, 2006 10:40 AM
To: Rich.Jesse_at_qg.com; oracle-l_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jesse, Rich Sent: Thursday, April 13, 2006 9:52 AM
To: oracle-l_at_freelists.org
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; And it errors out with:

ORA-00932: inconsistent datatypes: expected DATE got DATE

(In 10.2, the verbage is modified to "expected JULIAN DATE got DATE")

Add parenthesis and it works:

SELECT TRUNC(SYSDATE) - (TRUNC(SYSDATE) - TRUNC(SYSDATE)) FROM DUAL; I've been looking through the docs and Metalink, but I'm unable to answer "Why?". Anyone?

TIA!
Rich
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 13 2006 - 12:01:13 CDT