Search code examples
sqloracleto-dateto-char

ORA-01830 error with to_date

I'm trying to create a date subtract two days and then convert it to a char. For some reason I'm getting the following error:

ORA-01830: date format picture ends before converting entire input string

Here's my code:

 SELECT TO_CHAR(to_date('20-JUL-01 10:40:12')-2, 'dd-Mon-yy 24HH:MI:SS') as "Subtract 2 Days"
       FROM DUAL;

I'm not sure what's wrong, it seems to be an issue with the seconds

Solution

  • The Oracle default is a 12-hour clock with AM/PM. So, you need a date format for the date conversion:

    SELECT TO_CHAR(to_date('20-JUL-01 10:40:12', 'dd-Mon-yy HH24:MI:SS')-2,
               'dd-Mon-yy HH24:MI:SS') as "Subtract 2 Days"
FROM DUAL;

    Also, the correct 24-hour signifier is "HH24", not "24HH".