Extending supported languages for "since" date formats in Apex

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Although Apex (3.0.1) comes with 9 optional languages (German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese) that you can install to localize Apex on-line help plus some locale information like the "SINCE" date format mask, the developer can freely create applications for languages not comprised in this set (see the list of all supported language codes).

The package APEX_UTIL contains several useful procedures and functions and one of these packaged functions is:

APEX_UTIL.GET_SINCE(date);

This function returns a string representing in words the elapsed time since a certain date (<= SYSDATE) specified as a parameter. A typical usage is for indicating how old a certain entry is, for instance news, blog postings, forum messages, emails and so on. Note that Apex's SINCE date format mask embeds the function at the report attributes level, so you don't need to explicitly include it in the query source normally. Although APEX_UTIL.GET_SINCE is a globalized function, translated versions of the text require to install apex in target language(s), so it might come in handy the following apocryphal version that makes use of Apex's globalization techniques, that is by means of translatable text messages. This can be especially useful if you need to translate the strings in one of the languages that are not directly supported by Apex.
Bear in mind however that a separate copy of each translated message will be required for each installed application.

Download get_since.sql source file.

Another difference is in that i adopted a somewhat relaxed concept of time to describe how old is a certain date, but you can change the function to suit your needs by increasing, reducing or modifying the time ranges.

See an example of Apex's standard function compared with the custom version.

Besides English, I've loaded Spanish and Italian translations in the sample application, but you must have the browser language set accordingly in order to see them.
All other languages will display messages in English.

You can use the linked script to initialize the English text messages for a certain application or modify it for entering additional translations, just change the language code and the text.

These messages can be edited individually from the Text Messages page in the globalization section of the shared components within Apex.
Note also that you don't need to create a translated version of the application for displaying the text in the desired language.

Finally, a quick tip for dealing with a report query that includes this custom function, as the most typical situation is a news report sorted by descending publication date (see the picture below):

1. the query must contain both the column containing the date value as well as the result of get_since (in the picture columns PUBDATE and AGE respectively).
   
2. Check the sort option for the date column and leave unchecked the sort option for get_since result (as pictured above). This setting is necessary to sort items properly because get_since returns a string that would result in a meaningless alphabetical sort.
   
3. You can sort the report basing on the real date column without displaying it in the report. In this fashion you'll get a report displaying just how old are the entries, starting from the most recent to the oldest.
4. Remember that you can combine column values as you prefer, you can even group different columns inside the HTML Expression property of the report column attribute page (see picture below), the current column value can be manipulated using the #COLUMN_NAME# syntax (see #AGE# in the example below), in this way you can create reports formatted exactly as you want.