on Oct 18, 2017Article Content
| Article Number | 000035563 |
| Applies To | RSA Product Set: Identity Governance and Lifecycle RSA Product/Service Type: Enterprise Software RSA Version/Condition: 7.0 |
| Issue | For an Identity Collection from an Oracle HRMS system, for some dates, the data is not getting mapped to the RSA TERMINATION_DATE attribute. 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 | In some circumstances, the Oracle HRMS column EMP_TERM_DATE may contain the following data "4712-12-31 12:00:00.0". 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 | In the case where the Oracle HRMS column EMP_TERM_DATE has the value "4712-12-31 12:00:00.0", then having no data in the RSA attribute TERMINATION_DATE is expected behavior. This is because the Julian date value "4712-12-31 12:00:00.0" is equivalent to a NULL or empty date. |
| Notes | For more information on how the Oracle database handles dates, please review Doc ID 69028.1 - How does Oracle store the DATE datatype internally?, where you will need to login to the Oracle Support portal. Alternately, you can access the Oracle online documentation for the DATE Data Type. |