If you store timestamps as string values (varchar2
) in your Oracle database, you will need convert them to date or timestamp values in order to use sorting based dates and times.
Let’s say, you have this value in your table: 27-AUG-17 08.27.57.113444 PM -04:00
. Data type of the column is VARCHAR2
. If you use this column in your ORDER BY
statement, your query will return a table sorted based on string comparison. In this case, 27-AUG-17
will be bigger than 26-AUG-18
which is incorrect because the year 2018 is bigger than 2017.
While trying to convert these strings values to dates, you may come across these errors:
ORA-01830: date format picture ends before converting entire input string
ORA-01821: date format not recognized
Solution
Use the functions below to convert your strings to dates or timestamps.
Original value: 27-AUG-17 08.27.57.113444 PM -04:00
TO_DATE(SUBSTR(V_DATE_TIME,1,15), 'DD-MON-YYYY HH:MI')
Result: 27-AUG-17 08:27
TO_DATE(SUBSTR(V_DATE_TIME,1, 10), 'DD-MON-YYYY')
Result: 27-AUG-17 00:00
TO_TIMESTAMP_TZ(V_DATE_TIME, 'DD-MON-RR HH.MI.SS.FF AM TZH:TZM')
Result: 27-AUG-2017 08.27.57.113444000 PM -04:00
2 thoughts on “Solved: “ORA-01830: date format picture ends before converting entire input string” and “ORA-01821: date format not recognized””