How to convert LDAP date time stamps into Cache $HOROLOG

I was recently asked whether we have a function to convert LDAP date time stamps into $HOROLOG format or other formats and the answer is not at the moment, but there is a simple method to do the conversion.

Let us look at the facts and figures involved...

1) Active Directory's (AD) date 0 (zero) is 1601-01-01 00:00:00.000 or January 1st, 1601 at midnight (00:00:00)

2) AD timestamps are calculated as the number of 100 nanosecond intervals from date 0

3) 864000000000 is the number of 100 nanosecond intervals per day

4) The $HOROLOG format (Cache internal date) is a pair of numbers separated by a comma. The first number is the number of days since December 31st, 1840 and the second number is the number of seconds since midnight on the given day.

5) It is 87657 days from the 01/01/1601 to 01/01/1841, but not including the end date - so number of days past

All we need do then is find the integer value of the LDAP number divided by our number of intervals per day and subtract the number of days between our two start points. Then we must find the modulus of our LDAP number divided by our interval value and then divide it by 10 million, which is the number of 100 nanoseconds per second. This gives us the simple formula...

CacheDateTime = ((ADdatetimeString \ 864000000000) - 87657) _ "," _ ((ADdatetimeString # 864000000000) / 10000000)

...and we can convert that into a local date format string for example...

LocalDTString = $ZDATETIME(CacheDateTime,3)

...when we try this on a LDAP datetime value of "131221289250000000" we get a $HOROLOG of "64219,42525" and that converts to "2016-10-28 11:48:45"

…or to convert to local time including summer time adjustments use the ZDATETIMEH function…

LocalDTString = $ZDATETIMEH(CacheDateTime,3)

I hope this helps.

Steve