How to convert the date field number from a request form to a human readable date in RSA Via Lifecycle and Governance

[#1]

Article Number

000033456

Applies To

RSA Product Set: RSA Via Lifecycle and Governance (RSA Via L&G)
RSA Version/Condition: 6.9.1 and higher
 

Issue

When a date is entered into a control_type Date Field on a Request Form, the form converts the date to a number that is the passed on to workflows.

For example, take a simple form with just one date field:
 

Image description

2. On the next page, click Finish.
3. A new request is generated.
4. Find the request under Requests > Requests.
5. Click on the Name of the request and pull it up.
6. Click on the Processing Workflow of that request, and edit it.
7. Right click anywhere outside a workflow node and select Properties.
8. Click on the Runtime Data tab.
9. Note that the start date variable is stored as 146732496000  and not 6/30/2016, as shown here:

Image description

Resolution

This format represents epoch time, that is milliseconds since 1 January 1970.  The number can be converted back to a traditional date format with one of these constructs:

• In a SQL node  you could use the variable:

to_date('${jobUserData_PublicData_Regina_Date_Number_StartDate}'/1000/60/60/24 + to_date('01-JAN-1970','DD-MON-YYYY')) AS StartDate

• Or in SQL*Plus you could:

SELECT to_date('1467324960000'/1000/60/60/24 + to_date('01-JAN-1970','DD-MON-YYYY')) AS StartDate FROM dual;

 

Image description