Question
FPAI
US
Last activity: 20 Jan 2022 3:48 EST
OOTB difference in days calculation issue in report definition
Getting difference in days as 1 even though both dates are same. Please find the screenshot attached. When I pass as "MMM dd, yyyy" instead of current date time, it is working as expected. Can you please suggest?
Updated: 18 Jan 2022 19:30 EST
Maantic Inc
US
@Firoz Shaik could you confirm database (oracle?) and also confirm QC completed on and current date time column DB data type.
share QC completed on and current date time column value format in database.
FPAI
US
@JohnPaulRaja,C Yes, it is Oracle. Please find the 'QC Completed On' column in the screenshot. And current date is from function alias 'pxCurrentDateTime'.
Maantic Inc
US
@Firoz Shaik is QC Completed On column is timestamp datatype?
FPAI
US
@JohnPaulRaja,C Correct, timestamp.
Maantic Inc
US
@Firoz Shaik based on my analysis, pega calls function pxDifferenceInDays. if the database is Oracle and column types are timestamp. it applies TRUNC
<p:when test=".pyParameters(1).pyDBDataType =='TIMESTAMP' && .pyParameters(2).pyDBDataType =='TIMESTAMP'"> TRUNC({2}) - TRUNC({1}) </p:when>
I tried some sample data in live oracle as well. it is working fine.
Create table timestamp_test (id number, timestamp_1 timestamp, timestamp_2 timestamp);
Insert into timestamp_test values(1,systimestamp,systimestamp);
select * from timestamp_test;
select trunc(timestamp_1)-trunc(timestamp_2) as result from timestamp_test;
Ernst & Young LLP
MT