Departure dates appear as 12/31/2039

Products

• Information Centric Analytics

Issue/Introduction

When viewing a user entity detail page in the Information Centric Analytics (ICA) console, the fields Planned Departure Date and Actual Departure Date may contain the value 12/31/2039; however, the account's details in Active Directory or an integrated human resources management system (for example, PeopleSoft or Workday) show the account has no planned or actual departure date.

Environment

Version : 6.x

Cause

The absence of a planned or actual departure date value is being explicitly or implicitly transformed into an empty string rather than a NULL value at some point prior to reaching the data source integrated with ICA. When ICA processes the empty string, SQL implicitly converts the empty string value to '1/1/1900 12:00:00 AM', which in turn is handled by C# as an undefined date and is displayed in the console as '12/31/2039' (-1).

Resolution

Convert the empty string to a NULL value upstream of ICA. If this is infeasible or cannot be done, the conversion should be handled in the integration's data source query or through a function in the import rule mapping, preferably in that order.

The following is an example of performing the conversion using the data source query when the data source is a SQL database:

SELECT  CASE
            WHEN ActualDepartureDate = N'' THEN NULL
            ELSE ActualDepartureDate
            END AS "ActualDepartureDate"

Previously created records in the table dbo.LDW_Users that contain the value '1/1/1900 12:00:00 AM' in the column ActualDepartureDate will need to be updated to NULL values through the following script:

UPDATE RiskFabric.dbo.LDW_Users
SET    ActualDepartureDate = NULL
WHERE  ActualDepartureDate = '1/1/1900 12:00:00 AM';

After updating the data source query and converting the improper departure dates to NULL, run the nightly RiskFabric Processing job.