Formatting: Changing Column Headers for SqlPlus Query

Question

I'm having trouble with two formatting issues that I would really appreciate help with: 1) The Days Open column is displaying the number of days properly, but the column name is being overwritten by my conversion command, and 2) I need the Order Date (OOpenDate) to be displayed in the "MM/DD/YYYY" format

Code:

column O_Num heading 'Order|Number' format a6
column OOpenDate heading 'Order|Date' format a10
column (sysdate-OrderOpenDate) heading 'Days|Open' format a4

select O_Num, OOpenDate, to_char(sysdate-OOpenDate, '999')
    from Orders
    where Status = 'Open';

What its currently displaying:

Order    Order             
Number   Date         TO_C
------   ---------    ------
100      03-DEC-13    14 

What I want it to display as:

Order    Order        Days     
Number   Date         Open
------   ---------    ------
100      12/03/2013   14 

Thank you in advance!

Answer 1

The simplest approach would be to alias the "Days Open" column and apply the format mask to the alias

column days_open heading 'Days|Open' format a4;

select O_Num, OOpenDate, to_char(sysdate-OOpenDate, '999') days_open
  from Orders
  where Status = 'Open';

Otherwise, the column name in your column command would need to exactly match the expression in your SELECT statement (including the to_char, any spaces, etc.) That's possible but it generally makes things rather hard to maintain.