The Biggest Hidden Bug in Your APEX Application

There’s been some feedback I’ve received on this so read all the way through

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

1
2
3
-- 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:

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

1
2
3
4
5
select to_date('24-Jan-21', 'DD-MON-YYYY')
from dual;

-- Returns
24-jan-2021

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.

Update

Based on some Twitter feedback by Andy Sayer he pointed out that we could use the FX option in the date format to enforce format exact. Some examples:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select to_date('24-Jan-21', 'FXDD-MON-YYYY')
from dual;

-- Error
ORA-01862: the numeric value does not match the length of the format item

select to_date('24-Jan-2021', 'FXDD-MON-YYYY')
from dual;

-- Returns
24-jan-2021

-- But not including a "0" prefix for the day number will also result in an error
select to_date('4-Jan-2021', 'FXDD-MON-YYYY')
from dual;

-- Error
ORA-01862: the numeric value does not match the length of the format item

As you can see they’re some pros and cons to this approach. Documentation on the FX modifier can be found here

There was also some discussion about using the FM modifier in conjunction with the FX modifier to resolve the day padding problem. This doesn’t solve our two digit year problem as it will just be padded with 0s which means we’re back to our original problem as shown below. Documentation on FM modifier can be found here.

1
2
3
4
5
select to_date('24-Jan-21', 'FXFMDD-MON-YYYY')
from dual;

-- Returns
24-jan-0021