title: Weird behavior in Oracle date columns
body: <p>I am getting this weird behavior in Oracle 9i:</p>
  
  <pre><code>select
      t.final_day,
      to_char(t.final_day,'DD/MM/YYYY HH24:MI:SS') final_day1,
      t.week_first_day,
      to_char(t.week_first_day,'DD/MM/YYYY HH24:MI:SS') week_first_day1
  from  
      mytable t 
  where 
      t.rowid in ( 'AAAJ6QAAZAAACenAAW','AAAJ6QAAZAAACenAAT')
  order by 
      t.end_date
  </code></pre>
  
  <p>The weird thing is that dates that were inserted without a time (default to 00:00/12:00 AM) convert OK to chart, but dates that were inserted with a time fail to convert to char:</p>
  
  <pre><code>FINAL_DAY               FINAL_DAY1          WEEK_FIRST_DAY          WEEK_FIRST_DAY1 
  7/19/2015 12:00:00 AM   19/07/2015 00:00:00 7/13/2015 12:00:00 AM   13/07/2015 00:00:00
  7/25/2015 11:00:00 PM   00/00/0000 00:00:00 7/19/2015 11:00:00 PM   00/00/0000 00:00:00
  </code></pre>
  
  <ul>
  <li>What could be the reason for this and how could I work around it?</li>
  </ul>
  
  <p><strong>Update:</strong></p>
  
  <p>This is what I get when I apply the dump function to both date columns:</p>
  
  <pre><code>DUMP1                               DUMP2
  Typ=12 Len=7: 120,115,7,19,1,1,1    Typ=12 Len=7: 120,115,7,13,1,1,1
  Typ=12 Len=7: 120,115,7,26,0,1,1    Typ=12 Len=7: 120,115,7,20,0,1,1
  </code></pre>
  
  <p><strong>Update2:</strong></p>
  
  <p>Both columns are of type <code>DATE</code>. Behavior persists even after doing <code>alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';</code>.</p>