When i was debugging an issue in one of our java ee project, i found this topic was quite interesting.
the exception the QA got was: “ORA-01830: date format picture ends before converting entire input string.”
The stored procedure that threw this exception has a “where clause” like this:
WHERE PROCESSING_DATE = P_DATE_TIME
P_DATE_TIME is the parameter passed in to this stored procedure. And the value is something like: 30-Apr-2014 00:00. It is obvious that the parameter has a timestamp format: ‘dd-MON-yyyy hh24:mi’.
The PROCESSING_DATE is a column with a DATE type definition:
Name NULL TYPE ----------------------------- -------- -------------- PROCESSING_DATE DATE
So in the above statement “Where PROCESSING_DATE = P_DATE_TIME”, Oracle tries to implicitly convert ’30-Apr-2014 00:00′ to a DATE with the NLS_DATE_FORMAT of the session. You can query the NLS_DATE_FORMAT by query:
SELECT VALUE FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT'
In my case it is “DD-MON-RR”
So if the input parameter has a value “30-Apr-2014”, it has no issue. It will implicitly convert the varchar to a date type. (actually, the values 30/Apr/2014, 30-April-2014, 30-Apr-14 are all working correctly).
However, the best way is to explicitly convert the varchar to a date type.
Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons:
•SQL statements are easier to understand when you use explicit datatype conversion functions.
•Implicit datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.
•Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.
•Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.
To resolve this issue, the where clause should be:
WHERE PROCESSING_DATE = to_date( P_DATE_TIME, 'dd-MON-yyyy hh24:mi')
It explicitly convert the input parameter to date, and it does not need to change anything else.