PEGA treats string as date - and subtracts a day

We are having trouble getting PEGA to handle dates in reports. We have a summary view that drills-down to a list view. Because we can only pass strings between these reports, the date field in the database view that PEGA is using for the summary view is defined as varchar. When we pass this to PEGA and group by it, PEGA treats this field as a date, not as a string, before we get to the listview (i.e. the problem lies in the summary view).

The property is defined in PEGA as text and on the database view as varchar. Nowhere is it mentioned as a date, yet PEGA treats it as a date. We know this because PEGA keeps subtracting a day from it. For example, we are grouping work items by the week in which they are received. We have a database view that has a column, defined as varchar, containing the date of the beginning of the week (so if the date received property was 13-APR-2011, a Wednesday, the column in the view would show 11-APR-11, a Monday). When this is displayed in the summary view it shows as 10-APR-11, that is, PEGA has subtracted one day from the date even though it is a string!

Does anyone know why this happens? And is there a way to stop it happening?

Many thanks for your help,

Mike Finlay.

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.


Keep up to date on this post and subscribe to comments

April 27, 2011 - 9:27am

Did you check the pxResults page of the report? Does it have the same subtracted date?

April 27, 2011 - 10:02am

Yes, it does. What is weird is that PEGA does the date-subtraction even if the field is nothing like a date - as long as a date has been used in it's construction!

We did an extreme test earlier today - instead of passing a date, we passed the hard-coded string "ab2". PEGA used "ab2" as expected. But when we built "ab2" in the database view as:

'ab' || substr(to_char(receivedDate + 1,'YYYY-MM-DD'),10,1)

where receivedDate was 2011-04-11, PEGA displays "ab1" even thought he database view column is a VARCHAR showing "ab2". So it appears that PEGA somehow knows that a date was used in the construction of the field and thus applies its date logic (which is to subtract an hour for BST which makes the date 23:00 of the day before).

Very strange...

April 17, 2014 - 1:59am

Please let me know How to get Day(like Monday,Tuesday) from given date ?? Thanx in advance !!!