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

When you subtract two dates you get a number and sql follows order of operations so it goes from left to right.

Trunc(sysdate)- Trunc(sysdate) - Trunc(sysdate) becomes 1- trunc(sysdate) which throws the error as you cannot subtract a date from a number

With the parenthesis the way you have it you start with TRUNC(SYSDATE) - (TRUNC(SYSDATE) - TRUNC(SYSDATE)) which becomes TRUNC(SYSDATE) - 1 which is fine.

If you would switch the parenthesis to
(TRUNC(SYSDATE) - TRUNC(SYSDATE)) - TRUNC(SYSDATE) you would get the same
error as it would become 1- trunc(sysdate)

Ken Naim

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