Monday, February 13, 2017

Where do you specify the Date Format Mask

When reviewing Oracle APEX applications I often see hardcoded date or timestamp formats.
You can define your date formats in multiple places in your application. In your item or column attributes, as part of your code e.g.TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI’) or if you want to make it more reusable you might create a substitution string. That will all work, but you can make your life easier and for the ones looking or needing to maintain your code…

APEX itself provides in the Globalization attributes (Shared Components) a place where you can define your default date and format masks for your entire applications. I consider this a best practice to use those fields, as it’s defined in one logical place, so when you need to change your format, you do it once and you’re done. In your custom SQL and PL/SQL code you can also reference those format masks by predefined substitution strings:

  • APP_NLS_DATE_FORMAT
  • APP_DATE_TIME_FORMAT
  • APP_NLS_TIMESTAMP_FORMAT
  • APP_NLS_TIMESTAMP_TZ_FORMAT

e.g. TO_CHAR(sysdate, :APP_NLS_DATE_FORMAT)

Here’s a screenshot which shows which substitution string corresponds with which field:

Application Attributes - Globalization

You can define the format mask you want, or you can click the arrow to see most used format masks represented with an example. To make it a bit easier, I put the format mask (in red) next to it, so you see the underlying format mask more easily:

Possible date format masks defined in the pop-up

If you need to make the format mask dynamic, for example using different format masks for different language, APEX doesn’t allow you to translate that substitution string through Text Messages, but you can work around it by using your own substitution string and have that dynamically filled. In the Globalization Attributes you would add instead of a hardcoded format mask your own substitution string e.g. &MY_TRANSLATED_DATE.FORMAT.

3 comments:

Jones said...

Here is one successfully completed post which I did,
SQL>
SQL>
SQL> declare
2 v_dt DATE;
3 v_tx VARCHAR2(2000);
4 begin
5 v_dt := to_date('19:40','HH24:MI');
6 v_tx := to_char(v_dt,'YYYY-MM-DD HH24:MI:SS');
7 DBMS_OUTPUT.put_line(v_tx);
8 v_dt := to_date('11-FEB-2006','DD-MON-YYYY');
9 v_tx := to_char(v_dt,'YYYY-MM-DD HH24:MI:SS');
10 DBMS_OUTPUT.put_line(v_tx);
11 end;
12 /
2007-06-01 19:40:00
2006-02-11 00:00:00

PL/SQL procedure successfully completed.
Know more: AngularJS Training

Geert Engbers said...

I am experimenting using apex.oracle.com and have set my date formats in the shared components, globalization section as you pointed out.
I have created an Mobile application and want to restrict the dates (of a date field using the date picker). I used the minimum date and maximum date and set these both to the value 201703041103 (effectively restricting the allowed value to just one date 04-03-2017 11:03.

Running the application in a browser on my desktop I cannot change the date nor the time component (this is as exepected). The date field shows as 03/04/2017 --:03 AM. When pressing the up or down error in the date field the hours (11) appears. No other numbers appear (this is correct as I have limited the time to 11:03.

Why does not the hour show initially? How can I get rid of the AM suffix?

And why does the date field show as 03/04/2017 eventhough I have set the Application Date Format (in the Globalization section) to DD-MM-YYYY?


When running the same application on my phone the min and max date value are ignored and all dates and times are selectable.

Is this a bug?

Aleksei said...

Where I define the &MY_TRANSLATED_DATE.FORMAT?