title: Oracle 11.1 bug converting Julian day number to DATE or TIMESTAMP
body: <p><a href="https://stackoverflow.com/questions/10356100/optimizing-this-select-query-with-julian-date-column/10359557#10359557">This recent post</a> got me busy investigating Julian date conversions in Oracle, and I've come across what I believe is a bug in Oracle 11.1.  Test cases are:</p>
  
  <p>Case 1.</p>
  
  <pre><code>SELECT TO_CHAR(TO_TIMESTAMP('0', 'J'), 'DD MON SYYYY') FROM DUAL
  </code></pre>
  
  <p>This should return "01 JAN -4713" as defined <a href="http://en.wikipedia.org/wiki/Julian_day" rel="nofollow noreferrer">here</a>, but instead raises the error</p>
  
  <pre><code>ORA-01854: julian date must be between 1 and 5373484
  </code></pre>
  
  <p>Case 2.</p>
  
  <pre><code>SELECT TO_CHAR(TO_TIMESTAMP('1', 'J'), 'DD MON SYYYY') FROM DUAL
  </code></pre>
  
  <p>This should return "02 JAN -4713" as an extension of the above (one day later than the Julian zero date), but instead returns "01 JAN -4712" (off by a day less than a year).</p>
  
  <p>Case 3.</p>
  
  <pre><code>SELECT TO_CHAR(TO_TIMESTAMP('1721424', 'J'), 'DD MON SYYYY') FROM DUAL
  </code></pre>
  
  <p>returns "01 JAN 0001".  That's fine (as far as it goes).  If we then subtract 1 from the date value above we would expect it to return the previous day, i.e. 31 DEC -0001 (year zero does not exist); however, when we execute the following</p>
  
  <pre><code>SELECT TO_CHAR(TO_TIMESTAMP('1721423', 'J'), 'DD MON SYYYY') FROM DUAL
  </code></pre>
  
  <p>the following error is thrown:</p>
  
  <pre><code>ORA-01841: (full) year must be between -4713 and +9999, and not be 0
  </code></pre>
  
  <p>indicating that Oracle has attempted to generate a year of zero.</p>
  
  <p>(Note that although TO_TIMESTAMP is used in the test cases above, the exact same problems occur when TO_DATE is used).</p>
  
  <p>Does anyone know if</p>
  
  <ol>
  <li>These problems have been documented by Oracle?</li>
  <li>These problems still exist in 11.2?</li>
  </ol>
  
  <p>Share and enjoy.</p>
  
  <hr>
  
  <p>Per Phil's answer below, these issues still occur in 11.2.</p>
  
  <p>Cthulhu fhtagn.</p>
  
  <hr>
  
  <p>Same bugs in 10.2.0.4</p>