Question

9
Replies
1165
Views
SeemaB65 Member since 2016 1 post
Tata Consultancy Services
Posted: 3 years ago
Last activity: 3 years 9 months ago
Closed
Solved

User defined DB2 SQL functions not converting the date time to User time zone while extracting report from Report definition

We are trying to extract an excel report of cases by utilizing Report Definition and we have written an activity by passing Report deifintion to "pxRetrieveReportData" (OOTB Pega activity).

We have utilised SQL Functions to format the "pxCreateDateTime" for the excel report in Report definition. However when Report defintion extracts the results, it does not convert the formatted "pxCreateDateTime" to User time zone and instead provides results in MST time zone ( as the date time is stored in Pega Database in MST time zone).

The Pega database that were are using is IBM DB2 and following SQL function were tried to extract the pxCreateDateTime in User time zone. 

1. VARCHAR_FORMAT(CAST({1} AT LOCAL AS TIMESTAMP), 'MM/DD/YYYY HH:MI AM' )
Here {1} is pxCreateDateTime

2. VARCHAR_FORMAT(CAST( {1} AT TIME ZONE (CASE WHEN {2}='US/Eastern'
   THEN '-05:00' 
   ELSE
   '-07:00'
   END) AS TIMESTAMP), 'MM/DD/YYYY HH:MI AM' )
Here {1} is pxCreateDateTime and {2} is User time zone

However when the above SQL functions are used in DB2 console, they are providing expected results. But when these SQL functions are invoked from Pega; the results are not getting converted to User time zone.

Kindly let me know if any other alternative SQL functions in DB2 are available for time zone conversion along with formatting of date time. 
Also let me know if any one faced the similar issue with the usage of Report definition to extract the cases to excel report by utilizing SQL functions

Reporting
Moderation Team has archived post
Share this page LinkedIn