how to set a user preference date format in BIP for Excel Template

Question

Summary:

Either it should be mm/yy/dd or dd/mon/yyyy etc whatever user has setup in his preferences

Content (required):

Can you please help me in how can the date format should be set as per user preferences in BIP report.

in my case I'm displaying the PO creation date in Excel Template, so in the output the date format should be user preferences.

User_VO7FP · Answer

@Abhiengine Are you able to derive the date format from User preference? we do have the same requirement?

Thanks in advance.

Kalyan Reddy · Answer

@Abhiengine Are you able to derive the date format from User preference?  we do have the same requirement?

Thanks in advance.

Abhiengine · Answer

Hi Sumanth

Use the following sample SQL:

SELECT TO_CHAR(FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone (select :xdo_user_timezone from dual),'YYYY-MM-DD HH24:MI:SS ') creation_date, creation_date creation_date2, :XDO_USER_TIMEZONE, :xdo_user_name name FROM ap_invoices_all where

TRUNC(FROM_TZ(creation_date, DBTIMEZONE) at time zone (select :xdo_user_timezone from dual)) <=:p_date order by creation_date

This query only changed the time zone but not the date format. Here we are specifying the date format like

TO_CHAR(FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone (select :xdo_user_timezone from dual),'YYYY-MM-DD HH24:MI:SS ') .

I need to format date according to user preference , not by hard coding.