The Biggest Hidden Bug in Your APEX Application

Let's start with a little quiz. Without checking, what do you think happens/returns running the following query:

-- Note the two digit year
select to_date('24-Jan-21', 'DD-MON-YYYY')
from dual;

I recently asked this same question on Twitter and the results were quite interesting given that this should be a very straight forward answer. Over 50% of the people got it wrong:

{% asset_img twitter-results.png %}

The correct answer is that the query returns: 24-Jan-0021. If you thought an error would be raised you're in good company. I reached out to several Oracle ACEs (i.e. world leading Oracle experts) and they all got it wrong as well (myself included).

By using a date format of DD-MON-YYYY if a user does not explicitly enter a four digit year, Oracle will left pad the number with 0s. I.e. 21 becomes 0021. This makes sense that one would expect 5-Jan-2021 to really be 05-Jan-2021.

What does this have to do with your APEX application? In all my applications I tend to set the default date format to DD-MON-YYYY as it's very explicit. Note: to set the default date format in APEX go to Shared Components > Globalization > Application Date Format. Most of the Date items that I use allow users to either enter the date manually or select from the date picker (via button click). Since entering a two digit year is a valid date neither APEX or Oracle raises an error so it's extremely difficult to catch.

To get around this issue you can change the Application Date Format to: DD-MON-RRRR. From the Oracle documentation:

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

Using our initial example

select to_date('24-Jan-21', 'DD-MON-YYYY')
from dual;

-- Returns

The nice thing about using the RRRR format is you can still display years as four digits but users can enter them in as two digits with expected results. If users are entering past dates (ex: cataloging old library books) I suggest you read the documentation to see if the RRRR format is the right setting for your application.