Search code examples
sqloracle-databasedatedatetimedate-formatting

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

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.

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)