SQL to convert 18-digit Active Directory timestamps to datetime format for SCCM tables

EDIT: OK, I realize the @numberofminutes is static – 157258080. When I wrote this post, I got fixated on finding the value programmatically I went overboard and added some code that doesn’t need to be in there, like checking if the current year is a leap year, since the while loop stops at 1900. It may be helpful for other reasons, in case someone needs to find the number of minutes between two random dates, so I’m keeping it.

The 18-digit Active Directory timestamps are the number of 100-nanosecond intervals (1 nanosecond = one billionth of a second) since Jan 1, 1601 UTC.  These are stored in SCCM in various tables and views; for example:

lastLogonTimestamp0, badPasswordTime0, and lastLogon0 in User_DISC

pwdLastSet0 and lastLogon0 in System_DISC

Converting these to datetime values (to the nearest minute) requires the conversion of the value to the number of minutes since 1/1/1900, and then use DATEADD (minute, value, 0) to get the datetime value (use “0” since that is equivalent to 1/1/1900).

This code does the following:

Convert the timestamp to the number of minutes from 1/1/1601.

From that, subtract the number of minutes between 1/1/1601 to 1/1/1900 (finding the number of minutes between 1/1/1601 to 1/1/1900 also requires finding the number of leap days).  This results in the conversion of the timestamp value to be the number of minutes since 1/1/1900.

Add that to 1/1/1900.

/*
Any year evenly divisible by four is a leapyear,
except centesimal years (years endingin two zeros)
which are considered common years and thus have the typical 365 days,
unless they are evenly divisible by 400
https://www.accuracyproject.org/leapyears.html
*/
-- Figure out how many leap days from 01/01/1601 to 01/01/1900
declare @year int = 1601
declare @leapdays int = 0
declare @today varchar (8) = convert (varchar, getdate (), 112) -- Todays' date in YYYYMMDD format
while @year < 1900
begin
-- Find the number of leap days.  We don't want to count a
-- leap day if this is a leap year, but it hasn't happened yet.
 if      @year % 400 = 0 and @today > cast (@year as varchar) + '0228'
  set @leapdays = @leapdays + 1 -- when @year is divisible by 400, then yes
 else if @year % 100 = 0
  set @leapdays = @leapdays     -- when @year is divisible by 100, then no
 else if @year %   4 = 0 and @today > cast (@year as varchar) + '0228'
  set @leapdays = @leapdays + 1 -- when @year is divisible by   4, then yes
 set @year = @year + 1
end
/*
lastlogon is the number of 100-nanosecond intervals
(1 nanosecond = one billionth of a second) since Jan 1, 1601 UTC
https://www.epochconverter.com/ldap
Dividing by 600000000 yields the number of minutes since Jan 1, 1601 UTC.
The base date for the dateadd function:
"select cast (0 as datetime)" is "1900-01-01 00:00:00.000", so
subtracting the number of minutes in 299 years,
plus a variable number of @leapdays
*/
declare @numberofminutes bigint = (299 * 365 + @leapdays) * 24 * 60
select
  s.Name0
, s.Last_Logon_Timestamp0
, s.lastLogon0
, case when lastlogon0 = '' then NULL
 else
  DATEADD
  (
  MINUTE
  , (cast (lastlogon0 as bigint) / 600000000)
   - @numberofminutes
    + DATEDIFF (MINUTE, GETUTCDATE(), GETDATE()) -- Convert to local time
  , 0 -- starting from 01/01/1900
  )
 end lastLogonConverted
from v_R_System s
order by 4 desc

For whatever reason, the Last_Logon_Timestamp0 value in some of the tables do not seem to accurately reflect the actual value: