Question

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

Correct Answer
August 15, 2019 - 10:48am

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

Comments

Keep up to date on this post and subscribe to comments

Pega
August 13, 2019 - 6:11am

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"

August 13, 2019 - 6:30am

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", "", "")

August 13, 2019 - 7:28am

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.

August 13, 2019 - 9:26am

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 :(

 

 

August 13, 2019 - 9:38am

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 

August 13, 2019 - 9:52am

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

August 13, 2019 - 10:25am

Hi BHANUPRAKASHR

The Param type is DateTime only. I have tried all possible formats till now. No luck.

 

August 14, 2019 - 12:47am

Hi Bhanu,

I have uploaded a document with screenshots. Let me know if you find anything.

Thanks,

Alini

August 15, 2019 - 10:48am

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

August 16, 2019 - 12:19am

Hi BHANUPRAKASHR

Thanks a lot for sharing the details. It's working fine now.

Best!

Alini