Support Article
Report using a Text to Date function returns a wrong date
SA-19659
Summary
Some reports in the application include a Text property that contains date values which needs to be sortable in chronological order. In the database, this date is stored as a string so a SQL function alias rule is used to convert the text to a SQL Date. When the report is executed the date value is always one day behind the original date.
Error Messages
Not Applicable.
Steps to Reproduce
- Create a SQL function alias rule to convert a varchar column to a date.
- Use this function in a report definition.
- Run the report and observe the date value.
Root Cause
A defect or configuration issue in the operating environment:
The reported issue is a side effect of including SQL dates in Reports when these values don't belong to Pega Date and Time properties and the Application Server time zone is not UTC. The issue is not affecting Pega managed Date and a Time properties but only occurs in special circumstances (i.e.: dates from external sources, text to date conversions, application servers using different time zones). Pega takes case of neutralizing these issues when Pega Date or Date and Time properties are used to map Date attributes. However in this case the attribute is a text property which is converted to a date using a SQL function. This logic is custom and Pega is unaware of the conversion hence it can't address the time zone correctly. This would not have happened if the property was mapped as Date or Date Time.
Resolution
Use a SQL function that performs the time zone conversion on the Database side returning a date adjusted to Application Server time zone or use a custom HTML control to render the date time property as date.
E.g.: create a custom HTML Control saving the OOTB Export_DateTime control as Export_DateTime_Custom and changing line 8 of the HTML source
From: sDatetime = PRDateFormat.format(null, strTimeZone, PRDateFormat.DEFAULT_DATETIME_SHORT, curProp.toDate());
To: sDatetime = PRDateFormat.format(null, strTimeZone, "<your_date_pattern>", curProp.toDate());
.
Replace <your_date_pattern> with the preferred Java Date Pattern.
Published February 11, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.