TERMINATION_DATE from Oracle HRMS Authentication System is not getting stored in RSA Identity Governance and Lifecycle 7.0
Originally Published: 2017-09-20
Article Number
Applies To
RSA Product/Service Type: Enterprise Software
RSA Version/Condition: 7.0
Issue
The source Oracle HRMS column EMP_TERM_DATE is of type VARCHAR.
The target RSA attribute TERMINATION_DATE is of type DATE.
Therefore, for the user data query, the SELECT must use the TO_DATE function to convert the EMP_TERM_DATE, for example TO_DATE(EMP_TERM_DATE, 'YYYY-MM-DD HH:MI:SS').
However, even when this is done, the date is still not captured in the RSA attribute TERMINATION_DATE.
Cause
This is a Julian date, so it needs to be handled differently to a a normal Gregorian Date.
Further, Julian "day zero" is January 1, 4713 BC, so 4712-12-31 is the day before that, where this means it is the equivalent of a NULL date.
This value can also be seen in the Raw Data being collected.
Resolution
This is because the Julian date value "4712-12-31 12:00:00.0" is equivalent to a NULL or empty date.
Notes
Alternately, you can access the Oracle online documentation for the DATE Data Type.
