Oracle - Convert a String (containing a date) into a String (another date's format)

Question

I have an Oracle database which contains a table that have a column (String) that contains dates (in the java code, the toString() of that date is inserted into the String column) and the format is for example : Fri Feb 28 00:00:00 CET 2014

So i need to fix this to have a standard format => instead of "Fri Feb 28 00:00:00 CET 2014" i'll have "2014-02-28 00:00:00.0"

For future insertions in the database, the problem is fixed => usage of DataFormat instead of a toString() on the date.

BUT ! The problem is how to convert the entries already in the database !

I'd like to add that it is not possible to change the type of the column to Date so it must remain a String.

Any ideas ??

Answer 1

To answer your questions in the comments section about how to update the data, here is the code snippet.

SQL> c/testing/date_testing
  1* create table date_testing (t varchar2(30))
SQL> /

Table created.

SQL> insert into date_testing values ('Fri Feb 28 00:00:00 CET 2014');

1 row created.

SQL> update date_testing 
set t = to_char(to_timestamp_tz('Fri Feb 28 00:00:00 CET 2014', 'DY Mon DD HH24:MI:SS TZD YYYY'), 'YYYY-MM-DD HH24:MI:SS') || '.0' 
where length(t) = 28;

1 row updated.

SQL> select * from date_testing;

T
------------------------------
2014-02-28 00:00:00.0

Answer 2

You need to first convert into timezone and then reconvert into string of your required format. Here is the query.

select to_char(to_timestamp_tz('Fri Feb 28 00:00:00 CET 2014', 'DY Mon DD HH24:MI:SS TZD YYYY'), 'YYYY-MM-DD HH24:MI:SS') || '.0' from dual;

TO_CHAR(TO_TIMESTAMP_
---------------------
2014-02-28 00:00:00.0