title: ORA-01830 - date format picture ends... - but only in where clause - not in select
body: <p><em>I created an easier to test post here: <a href="https://stackoverflow.com/questions/22439654/solved-ora-01847-day-of-month-must-be-between-1-and-last-day-of-month-but-da">ORA-01847 day of month must be between 1 and last day of month - but data is OK</a>
  Problem solution described there</em></p>
  
  <p>It took me hours to search for a comparable issue in the web - finally i think only you out there can help me.</p>
  
  <p>I send my Query to Oracle 11 via SQL developer.</p>
  
  <p>You may ignore the complete subselect (its in pastebin) - i added a dump of each column to see details.</p>
  
  <p>I got big Problems when comparing timestamps in where clause: here my basic sql without where clause in last line:</p>
  
  <pre><code>select prod,operating,fakeday,prod_origin,operating_origin,
  dump(prod),dump(fakeday),dump(operating) from 
  (
  http://pastebin.com/wqX7dJPA
  ) sub
  -- works: where sub.operating &lt; to_timestamp('20140101','YYYYMMDD')
  -- works: where sub.fakeday &lt; to_timestamp('20140101','YYYYMMDD')
  -- works NOT: where sub.prod &lt; to_timestamp('20140101','YYYYMMDD')
  </code></pre>
  
  <p>Result without where clause is:</p>
  
  <pre><code>    'PROD'|                    'OPERATING'|                'FAKEDAY'|                  'PROD_ORIGIN'|'OPERATING_ORIGIN'|'DUMP(PROD)'|                                          'DUMP(FAKEDAY)'|                                              'DUMP(OPERATING)'
      25.11.13 00:00:00,000000000|25.11.13 00:00:00,000000000|25.11.13 00:00:00,000000000|',20131125,'|'25.11.2013'|      'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
      24.02.14 00:00:00,000000000|24.02.14 00:00:00,000000000|24.02.14 00:00:00,000000000|',20140224,'|'24.02.2014'|      'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
      28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|',20131028,'|'28.10.2013'|      'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
      28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|',20131028,'|'28.10.2013'|      'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
      20.01.14 00:00:00,000000000|20.01.14 00:00:00,000000000|20.01.14 00:00:00,000000000|',20140120,'|'20.01.2014'|      'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
  </code></pre>
  
  <p>If i add the where clause in last line filtering on 'prod' i'll run into </p>
  
  <pre><code>ORA-01830: Datumsformatstruktur endet vor Umwandlung der gesamten Eingabezeichenfolge
  01830. 00000 -  "date format picture ends before converting entire input string"
  *Cause:    
  *Action:
  </code></pre>
  
  <p>If i'm using the other column sub.operating or sub.fakeday i'll receive correct filtered resultlist.</p>
  
  <pre><code>where sub.operating &lt; to_timestamp('20140101','YYYYMMDD')
  </code></pre>
  
  <p>another important information - i can call</p>
  
  <pre><code>select prod,operating,prod-operating,fakeday
  </code></pre>
  
  <p>and it will work -> shows a interval result for prod-operating</p>
  
  <p>BUT </p>
  
  <pre><code>where prod - operating = INTERVAL '0' DAY;
  </code></pre>
  
  <p>will lead to: </p>
  
  <pre><code> ORA-01847: Tag des Monats muss zwischen 1 und letztem Tag des Monats liegen
   01847. 00000 -  "day of month must be between 1 and last day of month"
  </code></pre>
  
  <p>Please help - i don't have any idea.</p>