0

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 ??

Improve this question
2
  • Why can't you change the datatype to a DATE or DATETIME? Commented Jan 7, 2016 at 13:50
  • It is because there is so much code already using this column, internally and externally, so changing its format would break the api that we are exposing :( . Commented Jan 7, 2016 at 13:57

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I executed this but i have an error : ORA-01846: ce n'est pas un jour de semaine valide 01846. 00000 - "not a valid day of the week" Are you sure about this ?
Probably there are different date formats in your data set. Run this query select distinct length(column_name) from table_name; Once you get length try searching at least one record of different lengths by running select column_name from table_name where length(column_name) = 28
Yes you're right ! In fact there are 3 date formats (29,21,28). So the solution is no more usable ?
It is once you get all 3 different formats, above query has to be slightly modified.
I have added one more answer about how you can fix data for dates with 28 characters.
0

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
Improve this answer

5 Comments

Thank you for the snippet ! unfortunately even the execution of the "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;" (which is indepedant of my table and column) returns me the error
The same as earlier ORA-01846: ce n'est pas un jour de semaine valide 01846. 00000 - "not a valid day of the week" Tried to play with the NLS_TIMESTAMP_TZ_FORMAT (relatively to a session) but with no result.
This is weird cause it works on your database, so i think it should be the same on mine.
Probably, the language settings might be causing the issue.
I am trying to transform this date into an easy parsable one but i can't get it to compile :'( Could you look at it ? (I've edited my post)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.