Changing Report Column Date Format

In APEX there is build in date formats SINCE and SINCE_SHORT that I use often in report date columns. Unfortunately you can't use SINCE format in application Globalization Attributes, so you need separately set it to columns. But, sometimes you need to see the actual date value in column.

Here is simple example how you can switch between application global date format and SINCE.

First create application item G_DATE_DISPLAY_FORMAT.

Create two entries to Navigation Bar list as follows.
First list entry:

• List Entry Label: Show Since
• Target page: &APP_PAGE_ID.
• Request: SET_DATE_FORMAT_SINCE_ON
• Condition: Value of User Preference in Expression 1 != Expression 2
  • Expression 1: U_DATE_DISPLAY_FORMAT
  • Expression 2: SINCE

Second list entry is almost same. Just change label, request and condition

• List Entry Label: Show Date
• Target page: &APP_PAGE_ID.
• Request: SET_DATE_FORMAT_SINCE_OFF
• Condition: Value of User Preference in Expression 1 = Expression 2
  • Expression 1: U_DATE_DISPLAY_FORMAT
  • Expression 2: SINCE

Create On Load: Before Header application process.
PL/SQL Code:

declare
  l_since varchar2(40);
begin

  l_since := 
    case when :REQUEST = 'SET_DATE_FORMAT_SINCE_ON'
    then 'SINCE'
    end
  ;
  :G_DATE_DISPLAY_FORMAT  := l_since;

  /* save user selection to preference */
  apex_util.set_preference(
     p_preference => 'U_DATE_DISPLAY_FORMAT'
    ,p_value      => l_since
    ,p_user       => :APP_USER
  );

end;

Set process conditionally by request containing SET_DATE_FORMAT_SINCE_ON and SET_DATE_FORMAT_SINCE_OFF

Application process stores information also to preference. If you like you can set application item value for new sessions in Post-Authentication Procedure.

Place code to authentication schema source PL/SQL Code and Enter to Post-Authentication Procedure Name field set_date_display_format

procedure set_date_display_format
as
begin
  :G_DATE_DISPLAY_FORMAT := apex_util.get_preference(
     p_preference => 'U_DATE_DISPLAY_FORMAT'
    ,p_user => :APP_USER
  );
end;

Then edit report columns and set format mask to application item substitution string &G_DATE_DISPLAY_FORMAT.

Here you can see example where right top there is link to switch date format mask. When you click the link in navigation bar, you can see report Hired on column format changes.