How To Read Dates Before 1970

[Community_Admin]

Question

I have to display a field with dates such as 06/05/1851, 07/07/1891 and things like that.  I tried to workaround this by using CreateDate() but the data displayed is 01/01/1970.  Do you know how I can display the right date in Sisense?

Answer

Context: Data is pulled from Dynamics365 Online through a CData Connector into Sisense.  Dates are stored as  EPOCH Unix dates.  Therefore, dates prior 01/01/1970 are read as 0.

• First Step.  Cast the date attribute as VARCHAR when loading the entity.  See the example below:

• Second step.  Once in the ElastiCube Manager you must extract year, month and date and convert them into integer numbers.  Then, use the function CreateDate() to create the new date.  I used David Hogeg's approach to create the date.  Nonetheless, the approach was adapted to get year, month, day from different places in the string (Hogeg's assumes a YYYYMMDD format, while VARCHAR format is YYYY-MM-DDT....).  See the image below: