ORA-01830 error with to_date

Question

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

score 5 · Accepted Answer · answered Mar 09 '16 at 16:13

5

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".

answered Mar 09 '16 at 16:13

Gordon Linoff

I've got to say: This answer not only received an anonymous downvote (but it has other upvotes), but I also tested it an know that it is correct (although there could be a typo, of course). – Gordon Linoff Mar 09 '16 at 16:21
2

anonymous downvotes without cause seem awfully common lately around here. As do people not accepting any answers (just taking the help and running without acknowledgineg it - rude!), or accepting but not upvoting. six of my last eleven accepted answers got 0 upvotes. Not that I need a pat on the back, but yeah - taking a moment to show thanks for the help doesn't seem much to expect. – Michael Broughton Mar 09 '16 at 16:38
I can only guess that someone objected to the first sentence - do any territories show time components by default? And if the session NLS_DATE_FORMAT had HH and AM it wouldn't error with the OP's value, it would just assume it was a.m. Neither of which detracts from the correctness of the solution you gave of course. People are strange... – Alex Poole Mar 09 '16 at 17:48

1 Answers1