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:
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 :
NLS_DATE_FORMAT for every user that runs the query (and fix the hh format model to be hh24); orhh format model to be hh24); orTO_DATE as it is, at best, redundant (and, at worst, causes errors).Removing TO_DATE is the simplest solution.
to_char(order_date, 'dd/mm/yyyy')should do the trick. timestamp does not need to be casted to dateorder_date? The name of the column implies that it is a date. But dates do not have fractional seconds. Is it atimestamp?timestamp with time zone?timestamp with local timezone? Or is it avarchar2?