Sql – Convert yyyy/mm/dd hh24:mi:ss to dd-mon-yyyy format in Oracle

date, date-formatting, datetime, oracle, sql

Currently i have date in the

2006/01/01 00:00:00

What should i use to convert it into following format

01-JAN-2006.

Best Solution

if it's a string use

to_char(to_date('2006/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-YYYY')

If it's a DATE use

to_char(date_val, 'DD-MON-YYYY')

BUT whether you'll get JAN or national abbr of January depends on your NLS settings (session and database)