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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.