RSA IGL - Tips & Tricks: Business Friendly Date Formats in Workflows/Emails

[#1]

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 
  

YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.