Yesterday, one of the developers was having a problem and emailed to ask what was going on. They sent me a section of code from an old trigger that included some date handling that looked “interesting”.
TO_DATE(SYSDATE,'DD/MON/RRRR')
Some bright spark had decided this was the best way to trim the time component off a date, and unfortunately for us it worked for a very, very long time. Many years in fact.
Why was this causing a problem now? They were trying to do an update of a table that caused this to trigger to fire. The update was working fine from SQLcl, but failed when it was called from APEX, giving this error.
ORA-01843: not a valid month
Let’s take a look at what was happening here.
The SYSDATE function returns a date. The TO_DATE function expects a string as input, so Oracle does you a “favour” and does an implicit conversion from date to a string. How does it know how to do this conversion? It uses the NLS_DATE_FORMAT value for the session. What is the default value in our database?
SQL> SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') FROM dual;
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
----------------------------------------
DD-MON-RR
SQL>OK. So this is what is really happening, thanks to the implicit conversion.
TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR')
That’s lucky. Those formats will work with no errors. Phew.
SQL> SELECT TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR') FROM dual;
TO_DATE(T
---------
08-JUL-20
SQL>
So what’s the problem with APEX?
If I print out the NLS_DATE_FORMAT value from my APEX session it defaults to ‘DS’. What does that look like?
SQL> SELECT TO_CHAR(SYSDATE,'DS') FROM dual;
TO_CHAR(SY
----------
08/07/2020
SQL>
This is ‘DD/MM/YYYY’. Now I’m hoping you see the problem.
MON = MON
MM <> MON
We can see the result here.
SQL> ALTER SESSION SET nls_date_format = 'DS';
Session altered.
SQL> SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual;
Error starting at line : 1 in command -
SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual
Error report -
ORA-01843: not a valid month
SQL>
The implicit conversion used a different format mask that happened to be incompatible with the format mask used by the TO_DATE call.
In this case, they should have removed the time component using TRUNC(SYSDATE), but that’s not really the point of this post. Relying on an implicit conversion is *ALWAYS* a bug waiting to happen. In this case it took many years to surface, but the bug was always there. Waiting! APEX didn’t break the code. The code was already broken.
Date handling seems to mystify a lot of people, but it’s not that hard. You just have to pay attention and understand the functions you are using, rather than randomly combining things together until they appear to work.
Cheers
Tim…
PS. If someone says Oracle stores dates as strings, punch them in the face. I take no responsibility for the outcome of this action.
PPS. If you want to know more about Oracle dates, timestamps and intervals, you might want to look at this article.
