I have a column and its type is string and stores date something like this 03/1996 I want to convert that to 01-MAR-96?

how can I achieve that in ORACLE

  • 2
    Those that fail to learn from history are doomed to repeat it. Please do not use 2-digit years. – Michael Kutz Dec 6 '18 at 15:24
  • And you lose the ability to use several handy date functions and ability to do date arithmetic on that column, unless you use a to_date(bogus_date, 'dd-MON-YY') on that column, incurring a performance hit. The optimizer will also make stupid assumptions on that column, assuming there are values like '12-32-18' ... '12-99-18' etc between '12-31-18' and 01-01-18'. – Mark Stewart Dec 10 '18 at 16:08
SQL> select to_char(to_date('03/1996', 'MM/YYYY'), 'DD-MON-YY') from dual;


Based on that (if by convert you meant changing the data in the table):

update table tableX set columnX = to_char(to_date(columnX, 'MM/YYYY'), 'DD-MON-YY');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.