Support Article
Date Time Format incorrect when report is exported
SA-34761
Summary
When the user exports the report or schedules a report, the time format is fetched along with date, in Pega Customer Service 7.1.3.
Error Messages
Not Applicable
Steps to Reproduce
- Create a New Report and include a Date Time property.
- Add the control to display only the time.
- Run the report, property is displaying with the time alone.
- Click on Export to excel the property’s time format is in Date time format instead of time alone.
- Schedule this report and the report gets delivered to your mailbox. When user opens the excel, the property is having date time value.
Root Cause
A defect in Pegasystems’ code or rules.
When scheduling the report, the batch requestor runs an agent and takes the default short datetime format.
Resolution
Perform the following local-change:
Create a custom sql function alias with source as shown below in order to display time alone, in this case specific to Australian time zone:
<p:choose>
<p:when test=".pyParameters(1).pyUserEnteredDataType == 'DATE'">
(CASE WHEN {1} IS NOT NULL
THEN
SUBSTR({1},1,8)
END)
</p:when>
<p:when test=".pyParameters(1).pyUserEnteredDataType == 'DATETIME'">
(CASE WHEN {1} IS NOT NULL
THEN SUBSTR(TO_CHAR({1} at time zone 'Australia/Melbourne','YYYYMMDD hh24:mi:ss'),10,8)
END)
</p:when>
</p:choose>
Give this function in the column from the function builder in the report definition.
Published March 25, 2017 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.