Need help for conversion of datetime (2019-04-10 04:00:00.0) to 10-APR-19
I have pxcreateDateTime being saved as DD-MMM-YY format. So I cannot pass the exact Pega current date when I am supposed to compare and retrieve 120 days older cases from current date.
I tried FormatDateTime and it did not work. If there is any Pega OOTB function available request you to please share it here,
Thanks
Alini
Accepted Solution
TMHCC
GB
Hi AliniK,
Even the pxcreatedatetime is of different format in the data base, during run time Pega takes the base class property reference of pxcreateDatetime (DataTime) and that is the reason it is giving you the error of different types
Any specific reason for using only report definition?
You can try using the RDB-List which would also helps you in the performance and easy to query it.
Select Cast('27-Jun-18' as date) where Cast('27-Jun-18' as date)< DATEADD(dd, -120, DATEDIFF(dd, 0, GETDATE()));
Query for you would be in the RBD-List would be
Select * from Table_Name where cast(Column_name as date) < DATEADD(dd, -120, DATEDIFF(dd, 0, GETDATE()));
Thanks,
Bhanu Prakash
Pegasystems Inc.
IN
Hi
What error you are facing with Format date time?
Format/Reformat a DateTime string using the specified parameters. <br>
Return String formatted for specified locale, null if strDateTime is null <br>
<br>
Parameter: <br>
strDateTime: input time string <br>
strPattern: if null or blank, standard PegaRULES format, otherwise Java DateFormat pattern <br>
strTimeZone: timezone for output string, if null, the Operator's timezone is used. If Operator's TimeZone is not available then Server's default is used <br>
strLocale: locale to use for formatting, if null, the server's default is used <br>
<br>
<b>Example:</b>
FormatDateTime("20090109T162504.370 GMT", "MMMM dd,yyyy hh:mm:ss a", "America/New_York", "en_US") = "Jan 9, 2009 11:25:04 AM"
FormatDateTime("20090109T162504.370 GMT", HHmmss, "America/New_York", "en_US") = "112504"
FormatDateTime("20090109T162504.370 GMT", "yyyyMMdd", "America/New_York", "en_US") = "20090109"
The Toronto Dominion Bank
CA
Hi Santanu, Thanks for your response
I am getting blank values when I am using FormatDateTime
The way I am using it is: @FormatDateTime("Param.Date", "dd-MMM-yy", "", "")
TMHCC
GB
Hi Alinik10,
Pega is giving you the blank value since the Date Time which you are passing are not in the Pega suitable format for the function to work.
There is no OOTB function available suitable for your Date time Format, you might need to write your own function suitable for you , as an alternative you can convert the Date time into Pega supported Date time.
Please find the below screenshot for reference to convert into Pega suitable Date Time format.
The Toronto Dominion Bank
CA
I was able to convert it using these two functions:
Param.Date = @(Pega-RULES:DateTime).addToDate(@(Pega-RULES:DateTime).CurrentDateTime(), -120, 0, 0,0)
Param.Date = @(Pega-RULES:DateTime).FormatDateTime(Param.Date, "dd-MMM-yy", .pyTimeZone, null)
But now the report definition where I am using this Param.Date is throwing an error :
Name: .pxCreateDateTime, Literal Value: 15-Apr-19 - are not of the same type.
Not sure what is expected by the query :(
TMHCC
GB
Hi Alini,
You are trying to compare DateTime (pxCreateDateTime) property to a date(param.Date) format property which Pega doesn't accept. You need to have same type of property to compare the values.
Declare the param date as DateTime in the parameters tab and try(not sure if it works)
Thanks,
Bhanu
TMHCC
GB
Hi Alini,
Just for your Reference,
Even you save pxCreateDateTime in your wanted format, in run time it takes property reference from base class.
Thanks,
Bhanu
The Toronto Dominion Bank
CA
The Param type is DateTime only. I have tried all possible formats till now. No luck.
Accepted Solution
TMHCC
GB
Hi AliniK,
Even the pxcreatedatetime is of different format in the data base, during run time Pega takes the base class property reference of pxcreateDatetime (DataTime) and that is the reason it is giving you the error of different types
Any specific reason for using only report definition?
You can try using the RDB-List which would also helps you in the performance and easy to query it.
Select Cast('27-Jun-18' as date) where Cast('27-Jun-18' as date)< DATEADD(dd, -120, DATEDIFF(dd, 0, GETDATE()));
Query for you would be in the RBD-List would be
Select * from Table_Name where cast(Column_name as date) < DATEADD(dd, -120, DATEDIFF(dd, 0, GETDATE()));
Thanks,
Bhanu Prakash