convert oracle table column name to MM/DD/YYYY

Question

I would like convert the column name to date. for example the column name is today, i want to convert it dynamically to today's date like MM/DD/YYYY .

Answer 1

as of now the column name is "Today" i want it to be current date

You can't configure a column to change its name automagically. To reflect the current day or whatever else.

But, you can change the column name by using an alias when doing a query. In order to make the things the more transparent as possible, you might want to create a view. Here is an example:

-- Some table with a column  named "TODAY"
CREATE TABLE T AS (SELECT LEVEL today FROM DUAL CONNECT BY LEVEL < 5);


-- Use PL/SQL to create a view on the given table
-- with a dynamic column name
DECLARE
  today varchar(10) := TO_CHAR(SYSDATE,'DD/MM/YYYY');
  query varchar(200) := 'CREATE OR REPLACE VIEW V'
                     || ' AS SELECT today "' || today || '"'
                     || ' FROM T';
BEGIN
  execute immediate query;
END;

Then, to query the "table" with the right column name, you will simply need to query V instead of T:

SELECT * FROM V;

12/12/2014
1
2
3
4

If you recreate your view daily, say by calling the above PL/SQL code from a job, you will see each day a view with the current date as the column name. But, as the underlying table is left unchanged, you will still be able to query it using the canonical name today. Which is important for example if you need to perform join on that table.

---

That being said, I'm not sure I will push toward such a solution. Use at your own risks!

Answer 2

If you want the column name heading to appear as something different than what the column name is defined in the table, you simply use the as "DisplayColumnName" clause for that column:

select user_name, today as "12/12/2014" from some_table;

But you would need to programatically generate the SQL statement for that to work. What coding environment you are using would dictate how to dynamically create a select statement.