how to convert a date string like this MM/YYYY to 01-MM-YY

Question

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

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

Balazs Papp · Accepted Answer · 2018-12-06 12:30:08Z

1

SQL> select to_char(to_date('03/1996', 'MM/YYYY'), 'DD-MON-YY') from dual;

TO_CHAR(T
---------
01-MAR-96

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');

answered Dec 6 '18 at 12:30

Balazs Papp

32.6k2 gold badges13 silver badges36 bronze badges

add a comment |

1 Answer 1