Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

"TO_CHAR" is not recognized: with crmConvertDateToChar

SA-39287

Summary



When crmConvertDateToChar is used it throws an exception and does not work with SQL Server.

This occurs with Pega Customer Service for Healthcare 7.22.


Error Messages



"TO_CHAR" is not recognized


Steps to Reproduce

  1. Create a report definition.
  2. Set the function "crmConvertDateToChar" in column with a property Datetime.


Root Cause



This is an Oracle specific function, the TO_CHAR will work Oracle and DB2 which have equivalent functions at the database level, this is not available for SQL Server.

Resolution



A custom function will have to be written if a TO_CHAR is required for use with SQL Server.

There are examples on how to do this in the PDN and help files:

https://pdn.pega.com/how-create-custom-sql-functions-reporting

SQL Server which uses the CONVERT function to achieve the same thing as TO_CHAR but uses 3 parameters instead of 2 with the following format

CONVERT(TYPE, DATE, STYLE)

Refer to SQL Server documentation for further information on this function.



Published August 21, 2017 - Updated December 2, 2021

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us