Databases
StackOverflow2013
Postsdbo
POSubtracting Dates in Oracle - Number or Interval Datatype?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSubtracting Dates in Oracle - Number or Interval Datatype?
    primarykey
    data
    text
    <p>I have a question about some of the internal workings for the Oracle DATE and INTERVAL datatypes. According to the <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#g196492" rel="noreferrer" title="Oracle 11.2 SQL Reference">Oracle 11.2 SQL Reference</a>, when you subtract 2 DATE datatypes, the result will be a NUMBER datatype.</p> <p>On cursory testing, this appears to be true:</p> <pre><code>CREATE TABLE test (start_date DATE); INSERT INTO test (start_date) VALUES (date'2004-08-08'); SELECT (SYSDATE - start_date) from test; </code></pre> <p>will return a NUMBER datatype. </p> <p>But now if you do:</p> <pre><code>SELECT (SYSDATE - start_date) DAY(5) TO SECOND from test; </code></pre> <p>you get an INTERVAL datatype. In other words, Oracle can convert the NUMBER from the DATE subtraction into an INTERVAL type.</p> <p>So now I figured I could try putting in a NUMBER datatype directly in the brackets (instead of doing 'SYSDATE - start_date' which results in a NUMBER anyways):</p> <pre><code>SELECT (1242.12423) DAY(5) TO SECOND from test; </code></pre> <p>But this results in the error: </p> <pre><code>ORA-30083: syntax error was found in interval value expression </code></pre> <p>So my question is: what's going on here? It seems like subtracting dates should lead to a NUMBER (as demonstrated in SELECT statement #1), which CANNOT be automatically cast to INTERVAL type (as demonstrated in SELECT statement #3). But Oracle seems to be able to do that somehow if you use the DATE subtraction expression instead of putting in a raw NUMBER (SELECT statement #2).</p> <p>Thanks</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBYS2
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSubtracting Dates in Oracle - Number or Interval Datatype?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSubtracting Dates in Oracle - Number or Interval Datatype?
      1. USA.B.Cade
      1. VTFavorite
    3. VO
      singulars
      1. POSubtracting Dates in Oracle - Number or Interval Datatype?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODoes this happen when you put instead of sysdate something like to_date('20120216', 'yyyymmdd') ?
      singulars
      1. POSubtracting Dates in Oracle - Number or Interval Datatype?
      1. USA.B.Cade
    2. COHi sorry, didn't see your comment until now :S.. First to answer your question, yes it does this even if it use 'to_date()' instead of SYSDATE. On another note though, I managed to figure out how DATE subtraction is stored so I posted it as my answer :D.. since no one else on the internet seems to have written about this yet, i figured i'd write up what I found out.
      singulars
      1. POSubtracting Dates in Oracle - Number or Interval Datatype?
      1. USBYS2
    3. CO@BYS2 - Why would you put a number in () then try to subtract days from number and convert it to seconds: SELECT (1242.12423) DAY(5) TO SECOND from test? This is what you need to ask.
      singulars
      1. POSubtracting Dates in Oracle - Number or Interval Datatype?
      1. USArt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload