sql - Strings with TO_DATE command as value on the database

Question

I have a problem and I can't solve it yet. On my database I have the string:

TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN').

So when I'm building my query it is like the following:

Select my_field FROM my_table.

the result is:

TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Get it?

How to I extract the value of this field as a command statement on a select query?

Answer 1

On the sqlplus command line, if your system supports the NLS setting:

select TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') from dual;

The value of that field is the whole statement:

TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

I assume you want the actual date to print:

DECLARE
  foo varchar2(20):=NULL;
BEGIN
  select my_field 
  into foo 
  from my_table;
  execute immediate my_field;
END;
/

Tis is very cunbersome - what you really want is just the date string. But. This is what you asked for I think.

Something like this:

select substr(myfield, 10, 9) from my_table;

This is my take on it.