title: SPOOLING SQL SELECT statement to CSV File Oracle 12C - Date column comes out blank if not at far right hand side
body: <p>Im working on systemising some reporting which is done each day, I have created sql select statements which return the required data and I'm now looking to have these run overnight and saved to a location. </p>
  
  <p>We export and save files in a single string as CSV with a '<code>|</code>' delimiter, using this approach if i have a date inbetween other columns then this date column comes out blank. If i have it formatted as a string using <code>to_char(date, 'YYYYMMDD')</code> then it will work correctly however to work with it further in Excel or similar the user will need to convert this date. If i have this date field on the last column i.e. the further to the right then the date exports as expected without converting to string.</p>
  
  <p>Can anyone advise why this is the case and if there is a way to have date information with other columns either side?</p>
  
  <p>Code we are currently using which produces columns in the right order but date needs to be converted</p>
  
  <pre><code>WHENEVER OSERROR EXIT FAILURE ROLLBACK;
  SET SERVEROUTPUT ON SIZE 100000
  WHENEVER SQLERROR EXIT SQL.SQLCODE;
  
  SET TERMOUT OFF
  SET HEADING OFF
  SET TRIMSPOOL ON
  SET FEEDBACK OFF
  SET ECHO OFF
  SET PAGESIZE 40000
  SET LINESIZE 2000
  SET NEWPAGE NONE
  
  COLUMN filename    NEW_VAL filename
   SELECT 'FILEoutput'||TO_CHAR (SYSDATE, 'YYYYMMDD')||'.txt' filename
     FROM dual;
  SPOOL /data/out/&amp;filename
      select 'SKU'||'|'||'DATE'||'|'||'QTY'||'|'||'SEG' from dual;
          select dat.SKU ||'|'|| TO_CHAR (TRUNC (dat.date), 'YYYYMMDD') ||'|'|| dat.qty ||'|'|| dat.Seg 
          from scpomgr.dat;
  SPOOL OFF;
  EXIT;
  </code></pre>
  
  <p>If I remove the to_char then I get a blank column:</p>
  
  <pre><code>COLUMN filename    NEW_VAL filename
   SELECT 'FILEoutput'||TO_CHAR (SYSDATE, 'YYYYMMDD')||'.txt' filename
     FROM dual;
  SPOOL /data/out/&amp;filename
      select 'SKU'||'|'||'DATE'||'|'||'QTY'||'|'||'SEG' from dual;
          select dat.SKU ||'|'|| TRUNC (dat.date) ||'|'|| dat.qty ||'|'|| dat.Seg 
          from scpomgr.dat;
  SPOOL OFF;
  EXIT;
  </code></pre>
  
  <p>But if I put the date at the right it comes out correctly:</p>
  
  <pre><code>COLUMN filename    NEW_VAL filename
   SELECT 'FILEoutput'||TO_CHAR (SYSDATE, 'YYYYMMDD')||'.txt' filename
     FROM dual;
  SPOOL /data/out/&amp;filename
      select 'SKU'||'|'||'QTY'||'|'||'SEG'||'|'||'DATE' from dual;
          select dat.SKU ||'|'|| dat.QTY  ||'|'|| dat.SEG ||'|'|| TRUNC (dat.date)
          from scpomgr.dat;
  SPOOL OFF;
  EXIT;
  </code></pre>
  
  <p>These issues are only found if I run the export job and look at the CSV, if I just run these select statements everything looks OK.</p>
  
  <p>We are using Oracle 12C</p>
  
  <p>I've tried googling and searching for an answer but have been unsuccessful in finding the same scenario - Would appreciate any help or direction on this,</p>