Report Definition not accepting Date value in where clause

User Experience
, , , , ,
1

Hello,

I have used Data page to show the cases in the user work queue. In data page , I have used report definition as a source to fetch the results. But I am not able to see any results in user work queue because of the below error.

In where clause of the report definition, I have used date property in left hand side and used the Pega provided date option “Today” in the right hand side with relationship as less than.

Ex: TestDate < Today

When I try to run the RD with above condition, following exception is getting triggered and same has been captured in logs as well.

Error:

An error occured on executing the query for the report definition - Database column in database pegadata, table has an unknown type: JDBC Type: Date

Displaying 0 records

Based on the observation, Function “Today” has been considered as datetime whereas not date. Tried the below the approach but it didn’t worked.

  1. Tried to pass current date as parameter.

  2. Tried to pass a page which contains current date property.

Kindly share your suggestion to resolve the issue.

Thanks,

Seetharaman

2

@Seetharaman Hey,

Did you optimized this Date property? and are you seeing the column in DB table?

3

Hi @BATTINIRAMESH,

Yes Property is optimized and it is exposed to DB table.

4

@Seetharaman if your requirement is to display assignments created before today,

then please use

.pxCreateDateTime< Today

If based on cases create date , please join report with work class and

Work. pxCreateDateTime< Today

If you are trying to pass current date, then current date<today will not give any results.

We have function alias to convert text to datetime, but no function alias to convert date to datetime.

If you want to use current date in left hand side(column source), of filter →

Please click on fx-> and choose function-> The current system date and time as a Datetime value

5

Hi @Anoop Krishna

Please note that my requirement is not to show cases based on creation date. “Test Date” which I have mentioned is a clipboard property specific to Work object.

Also I passed the current date as param to right side of the where clause not to the left side. As “Today” is considered as datetime.

Left Side - TestDate → Date property

Right side - Today → Pega provided

TestDate < Today → 06/01/2023 < 06/06/2023

Functions provided in the where clause are related only to Datetime whereas not to date. Also it is not accepting the customized function in the right side . Only populated functions can be used.

Please suggest how to compare 2 dates ( only date) in the where clause of the report definition.

6

Hi @Seetharaman, your observation is correct you cannot directly compare the date property with “Today” or any other OOTB values because they work only for date time properties. Also, I don’t think there’s an OOTB function which does the conversion of date to date time within the RD itself.

The only possible solution that I could imagine was to have a new exposed property, let’s say CurrentDate, it will be linked to a declare expression which in return will calculate current date using the @CurrentDate() function and this property can be then used on the right hand side of your RD’s condition.

7

Hi @SaurabhG7134,

Yes this approach is working. Now I am able to view the results in work queue.

Thanks for the help!

8

@Seetharaman you can use Data Page with Data transform.

In Data transform set the currentdate to date property. and refer the Data page in Filter right side value as “Datapage.DatePropertyName”. Make sure in pages and classes define the data page

9

@Seetharaman

  1. Comparing datetime with datetime

I would suggest you to use ootb today filter in the rhs, that is datetime and in lhs-> your property converted to text and that converted to datetime.

So you would need a property holding text value of your date property, and then you can use function alias to convert that text value to datetime.

https://support.pega.com/question/date-string

2nd-> comparing date with date

You can do this way also. Converting current datetime to your date format and comparing them both

https://support.pega.com/question/how-convert-datetime-specific-date-format

10

Hi @Renuka,

Yes I tried this approach. If we run the Rd standalone with above approach its working fine. But during run time again we are getting the same error message.