Date format update in Oracle doesn't work

Question

I have a varchar2 field that contains only dates (for any unknown reason), but some registers are not "to_date" formatted.

I need to make a query using date format [for example: select * from users_table where status_date > '2014-01-21'], but appears the message "ORA-01861: literal does not match format string".

So I thought I could update everything for the date format using this code:

    update users_table set status_date = to_date(status_date, 'dd-mm-yy') where status_date > '2014-01-21';

It executes, but no register has changed. Please, how can I solve this issue?

Answer 1

I'm not sure this accomplishes what you ultimately want, which is to avoid different date formats in your table. What I would do is, like @DanBracuk suggests, add a new column to the table with a date type, using "alter table".

alter table users_table add (actual_status_date date);

Then change your query to put the result of to_date into the new column (making sure your formats match like @MaheswaranRavisankar suggests). I suspect that you may have more than one format in your varchar date table, where the 'dd-mm-yy' format string will not match. So this may be manual and iterative. Continue to pick up the rest of the varchar formats, placing date types in your new column until there are no empty date columns. Then get rid of your varchar date column and never do that again :)

You might then add a constraint to the new data column if that applies in your case, like a not null constraint.

alter table users_table modify actual_status_date date NOT NULL;

And if you do that, make sure the constraint ends up in any table creation scripts you have.

Answer 2

This will again throw error, if the status_date has date strings in multiple format.

    update users_table 
    set status_date = to_char(to_date(status_date, 'dd-mm-yy'),'YYYY-MM-DD') 
    where to_date(status_date ,'dd-mm-yy') > to_date('21-01-2014','DD-MM-YYYY');