How To Read Dates Before 1970

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: