Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: I was told there would be no (date) math

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

From: Ken Naim <kennaim_at_gmail.com>
Date: Thu, 13 Apr 2006 12:03:05 -0500
Message-ID: <00b401c65f1c$22db7ad0$fd346f44@KenHome>


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
Received on Thu Apr 13 2006 - 12:03:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US