SPOOLING SQL SELECT statement to CSV File Oracle 12C - Date column comes out blank if not at far right hand side

Question

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.

We export and save files in a single string as CSV with a '|' 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 to_char(date, 'YYYYMMDD') 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.

Can anyone advise why this is the case and if there is a way to have date information with other columns either side?

Code we are currently using which produces columns in the right order but date needs to be converted

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/&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;

If I remove the to_char then I get a blank column:

COLUMN filename    NEW_VAL filename
 SELECT 'FILEoutput'||TO_CHAR (SYSDATE, 'YYYYMMDD')||'.txt' filename
   FROM dual;
SPOOL /data/out/&filename
    select 'SKU'||'|'||'DATE'||'|'||'QTY'||'|'||'SEG' from dual;
        select dat.SKU ||'|'|| TRUNC (dat.date) ||'|'|| dat.qty ||'|'|| dat.Seg 
        from scpomgr.dat;
SPOOL OFF;
EXIT;

But if I put the date at the right it comes out correctly:

COLUMN filename    NEW_VAL filename
 SELECT 'FILEoutput'||TO_CHAR (SYSDATE, 'YYYYMMDD')||'.txt' filename
   FROM dual;
SPOOL /data/out/&filename
    select 'SKU'||'|'||'QTY'||'|'||'SEG'||'|'||'DATE' from dual;
        select dat.SKU ||'|'|| dat.QTY  ||'|'|| dat.SEG ||'|'|| TRUNC (dat.date)
        from scpomgr.dat;
SPOOL OFF;
EXIT;

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.

We are using Oracle 12C

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,

Answer 1

Using the code

TO_CHAR (TRUNC (dat.date), 'DD/MM/YYYY') 

then presents the data correctly and allows the column to be placed between other columns instead of at the end which solves the original question.