List Of Date Format Conversions

[Community_Admin]

Question

A date stored in a source system is not a datetime that Sisense recognizes. Converting it to a Sisense datetime allows powerful front end capabilities (such as drill down, quick functions and more).

Answer

Julian

AddDays(CreateDate(1970,1,1), ((([Julian]- 210866803200000000) / 1000000)/86400))

Julian (Oracle specific)

CreateDate(2000+floor([JulianOracle]/1000)-100,GetMonth(AddDays(CreateDate(2016,1,1),Floor(mod([juliandate],1000)))),1)

or

AddDays(CreateDate(1900+([JulianOracle]/1000),1,1),ToInt(Right(ToString([JulianOracle]),3))-1)

Convert Text mm/dd/yy

CreateDate(

ToInt('20'+StrParts([date],'/', 3)),

ToInt(StrParts([date],'/', 1)),

ToInt(StrParts([date],'/', 2))

)

Epoch

AddDays(CreateDate(1970,1,1), ([EPOCH DATE]/1000))

Date to UNIX

ToBigInt((DayDiff([date_field], CreateDate(1970,1,1)) * 86400))