Convert date/time format to Active Directory timestamp

  • 14 August 2015
  • 0 replies
  • 766 views

Badge +1

I recently had a request to get the AD user accounts expiring in 30 days from today's date, using Nintex Workflow.

 

The Background:

An AD timestamp (AKA LDAP time, WinTime or FileTime) is the number of 100-nanoseconds since the year 01/01/1601 @ 00:00.

 

There's a number of online tools that help us calculating this. For example,

    • To get the number of 100-nanoseconds between the years 01/01/1601 @ 00:00 and 01/01/2016 @ 00:00,

                     we can use silisoftware online free tool

108699_pastedImage_2.png

                    So, 130960980000000000 is the number of 100-nanoseconds between 01/01/1601 @ 00:00 and 01/01/2016 @ 00:00, and it's also the AD timestamp for 01/01/2016 @ 00:00

 

108700_pastedImage_7.png

The Challenges:

  1. Convert date/time format to AD timestamp.
  2. Convert the AD timestamp produced from the calculations, to a text readable by AD.

 

The Solution:

  1. To convert date/time format to AD timestamp:

Nintex allows us to get the number of seconds since 01/01/1900 @ 00:00, so we will have to do 3 steps to get the number of 100-nanoseconds since 01/01/1601 @ 00:00:

  1. Get the number of seconds since 01/01/1900 @ 00:00, save in varNum_secsSinceJanFirst1900.
    1. In a "Set variable" action:

Set:

YourNumberVariable (varNum_secsSinceJanFirst1900)

 

 

 

 

Equals:

Workflow Data

YourDateTimeVariable

(varDate_TodaysDate)

Number of Seconds since 00:00:00 1/1/1900

 

108704_pastedImage_8.png

 

2. Multiply var_secsSinceJanFirst1900 by 10^7 (100-nanosecs), save result in var_100nanoSecsSinceJanFirst1900

    • So, varNum_100nanoSecsSinceJanFirst1900 = varNum_secsSinceJanFirst1900 * 10^7

108705_pastedImage_24.png

 

3. Add the number of 100-nanoseconds between 01/01/1601 @ 00:00 and 01/01/1900 @ 00:00, to varNum_100nanoSecsSinceJanFirst1900, save result in varNum_requiredDateinADTimeStampFormat.

              • The number of 100-nanosecs between 01/01/1601 @ 00:00 and 01/01/1900 @ 00:00, taking into consideration the leap years is: 94354848000000000, you'll have to trust me on this.

108706_pastedImage_45.png

 

The calculations combined are as follows:

varNum_requiredDateinADTimeStampFormat = (varNum_secsSinceJanFirst1900 * 10^7) + 94354848000000000

 

 

2. To convert the AD timestamp produced from the calculations, to a text readable by AD:

The result of the calculations done above, is something like this: 1.3086031037335E+17, if you pass the number in this format to AD, AD won't recognize the number as an AD timestamp, and you will get zero result.

108810_pastedImage_0.png

An AD timestamp is an 18 digit number, so we need to convert 1.3086031037335E+17 to 130860310373350000. We can do this by following these steps:

  1. Convert the varNum_requiredDateinADTimeStampFormat number variable to text, save in varText_requiredDateinADTimeStampFormat.
  2. Apply the following inline functions to varText_requiredDateinADTimeStampFormat:
    1. Remove the dot ('.'):
      1. fn-Replace({varText_requiredDateinADTimeStampFormat},.,)
      2. Store in the same field: varText_requiredDateinADTimeStampFormat
    1. Remove 'E+17':
      1. fn-Replace(varText_requiredDateinADTimeStampFormat,E+17,)
      2. Store in the same field: varText_requiredDateinADTimeStampFormat
    2. Add zeroes to complete 18 digits:
      1. fn-PadRight(varText_requiredDateinADTimeStampFormat,18,0)
      2. Store result in the same field: varText_requiredDateinADTimeStampFormat

 

 

This how it looks combined:

Replace WorkflowVariable:sl_12hrsBeforeADTimeStamp in the below screenshot with WorkflowVariable:varText_requiredDateinADTimeStampFormat

108804_pastedImage_57.png

 

The result of the above string operations should be something like this: 130860310373350000, when you pass this to AD, you should get the expected results.

108805_pastedImage_58.png

 

The whole workflow is as follows (attached):

108806_pastedImage_59.png

108807_pastedImage_60.png

108808_pastedImage_61.png

108809_pastedImage_62.png


0 replies

Be the first to reply!

Reply