Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Report using a Text to Date function returns a wrong date



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.


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

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us