Date formats in workflows, typically come in a very long and "un-useful" format, which is: "YYYY-MM-DD HH:MM:SS" eg: 2019-11-05 10:46:54.0

This format is not very useful for business users and so changing it to be something more "readable" is a good idea in general. 

Thanks to the help from our resident rock star...Mostafa Helmy‌, we have the following example that you can use today for your review esclation emails. The below could be applied to any dates in other areas of the product too, however review esclations is a widely used area and a good place to start.

The process is as follows, where we want to update the review "end date" value.You could apply this to the "start date" too, if you wanted.

1. First, establish the format of how you want the date to look. e.g.. "29-01-2019" or "Feb-22-19" etc..
2. To do this, open a query analyser (e.g. SQL Developer) and test with the following SQL to find the date you want to display.
   Note, this example has 2 different options to get you started. Also, see the table below to get more ideas of the date formats that you could use.
    

   select to_char(sysdate,'MM/DD/YYYY') as new_date from dual;
   
   select to_char(sysdate,'DAY DD-MON-YY') as new_date from dual;

   ‍‍‍
   Image of the above SQL example

   

3. Once you have the correct format that you want to use

   Always start in DEV, NOT prod

4. Log into RSA IGL as a user who can edit/create workflows
5. Find the workflow/escalation you want to edit and open it
6. Add a new "SQL Select" object to the workflow, at the start

   It must be before the "email" or what ever you want this better date format to be

7. Edit the "SQL Select" object to be the following SQL.

   Note: This SQL will create a new value called "new_end_date" which can then be added into your email.

   Format	SQL
   MM-DD-YYYY (08/21/2019)	select to_char(end_date,'MM/DD/YYYY') as new_end_date from pv_review where id='${jobUserData_acm.ReviewId}'
   DD-MM-YYYY (21/08/2019)	select to_char(end_date,'DD/MM/YYYY') as new_end_date from pv_review where id='${jobUserData_acm.ReviewId}'

8. Set the Variable Type to Job. This must be set so that it can be referenced as a variable within the workflow
9. Make sure you now hit "SAVE"

   The new value wont show until you have done this!!

10. Select your email node and "right-click" where you want to add the new value (new_end_date) to add it in. (See image below)
11. Test this has worked by generating a new email and confirming the date format is correct and as expected.
12. Migrate to production

Other formatting suggestions:

Note:

• The format, MUST be in the correct CASE
• See this link for Oracle datetime format models: Format Models