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:
-- Note the two digit year
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
0021. This makes sense that one would expect
5-Jan-2021 to really be
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')
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.
select to_date('24-Jan-21', 'FXDD-MON-YYYY')
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.
select to_date('24-Jan-21', 'FXFMDD-MON-YYYY')