Converting timestamp to date format in Oracle 12c

Question

I have a below date value stored in a column in a table. I wanted to get the result in dd/mm/yyyy format only.

15-JUL-18 22.04.11.587000000

I am using this format but seems not rendering the correct output. Could you please help what would be the correct SQL query please.

select to_char(to_date(order_date, 'dd-mon-yyyy hh:mi:ss'), 'dd/mm/yyyy') from Titan_order;

Error:

ORA-01849: hour must be between 1 and 12 01849. 00000 - "hour must be between 1 and 12" *Cause:
*Action:

Answer 1

If you have a DATE or a TIMESTAMP and want to format it then use TO_CHAR:

SELECT TO_CHAR(order_date, 'dd/mm/yyyy')
FROM   Titan_order;

---

As for why your existing query does not work. NEVER use TO_DATE on a value that is already a DATE or a TIMESTAMP as TO_DATE takes a string as a first argument so your query would implicitly be:

SELECT TO_CHAR(
         TO_DATE(
           TO_CHAR(
             order_date,
             (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
           ),
           'dd-mon-yyyy hh:mi:ss'
         ),
         'dd/mm/yyyy'
       )
FROM   Titan_order;

As you have found, if the NLS_DATE_FORMAT session parameter does not match 'dd-mon-yyyy hh:mi:ss' (and hh is for a 12-hour clock not 24-hour clock, which is hh24) then your query will fail.

To fix it, you would need to either :

• change the NLS_DATE_FORMAT for every user that runs the query (and fix the hh format model to be hh24); or
• explicitly convert the date to a string and specify the format model in the query (and, again, fix the hh format model to be hh24); or
• as per the top of this answer, remove the TO_DATE as it is, at best, redundant (and, at worst, causes errors).

Removing TO_DATE is the simplest solution.