Convert AD time to real time.

Published on
10,183 Points
4 Endorsements
Last Modified:
I recently had a command, from on high, to find the last login times of users from multiple separate Active Directory (AD) domains and other login info.

So I Google around and find a dsquery command below. Then I find out later there userAccountControl chunk in the limits it pretty much to some attribute like built-in users or something like that. Use this one instead to get all your users and times:
dsquery * -filter "(&(objectCategory=user)(objectClass=user))" -limit 0 -attr distinguishedName sAMAccountName lastLogon > \\\LogonData\%COMPUTERNAME%_logontimes.txt

Open in new window

This gets you the last login time and dumps it to a file on a server with the computer's name from a DOS prompt.  Take off the Greater Than sign (>) and the rest to see it on screen.

Now Microsoft, in it's infinite wisdom, decided to store the AD time as milliseconds since 01/01/1753. I'm guessing the the days are the ideas of Babbage or the oldest living relative of Bill Gates.

So now I'm seeing the number in 129956570270092500 as the last login time. That is about useless to the average human. So some more Googling and I come up with the formula to get it to normal human dating.  Since I am probably going to to be tasked with building this report on a regular basis I decided to build a function for use in MS SQL Server DB that can convert it on a regular basis.

Here's the function and sample tests:
if object_id('[dbo].[fn_GetLastLogonDt]') is not null
	drop function [dbo].[fn_GetLastLogonDt]


create function [dbo].[fn_GetLastLogonDt](@LastLogonStamp bigint)
returns datetime
*    Author:		Jim P.
*    Last modified:	02/07/2013
*    Purpose:		Converts the last logon microsecond 
*					from Active Directory into a regular
*					date value

declare @LastLogin		as		bigint
declare	@ReturnDt		as		datetime

--	If the timestamp is too large, or small you get an error.
--	so I'm putting in an error handling routine to give an
--	obviously false date to handle that.

if	@LastLogonStamp < 47967800000000000 or
	@LastLogonStamp > 2650468000000000000
		select @ReturnDt =  case 
				when	@LastLogonStamp > 2650000000000000000	then	cast('9999-12-31 23:59:59:59' as datetime) --@ReturnDt =
				when	@LastLogonStamp < 47967800000000000		then	cast('1753-01-01 00:00:00.000' as datetime)  -- 1753-01-01 00:00:00.000
		set @LastLogin = @LastLogonStamp
		set @LastLogin = @LastLogin / (60 * 10000000)
		set @LastLogin = @LastLogin / 1440
		set @LastLogin = @LastLogin - (datediff(dd, '1/1/1900', dateadd(yy, 299, '1/1/1900')))
		select  @ReturnDt = dateadd(day,@lastlogin,'1/1/1900')

return  @ReturnDt


select	[dbo].[fn_GetLastLogonDt](129956570270092500)
select	[dbo].[fn_GetLastLogonDt](4000000000000000)
select	[dbo].[fn_GetLastLogonDt](46000000000000000)
select	[dbo].[fn_GetLastLogonDt](2750000000000000000)
select	[dbo].[fn_GetLastLogonDt](1650468000000000000)

Open in new window

Note that SQL Server can handle date as a date value from 01/01/1753 to 12/31/9999.

I figured this is a good way to pass on the knowledge to anyone who is searching for it. This function was built on SQL Server 2005 Express, but I can't see a problem with it working with any version from 2005 onward.

As a further information, this DSQuery is run along with the following query:
dsquery user -limit 0 | dsget user -dn -samid -ln -fn -display -mustchpwd -canchpwd -pwdneverexpires -disabled -acctexpires  > \\\LogonData\%COMPUTERNAME%_logonnames.txt

Open in new window

This gets the rest of the information on the users, such as disabled and expiration. I built a VBA routine, with the help of other exports, to import the data into Access on the way to the SQL DB.
Author:Jim P.

Administrative Comment

by:Eric AKA Netminder

Congratulations! Your article has been published. I hope others find this tip as handy as I did.

Page Editor
LVL 61

Expert Comment

by:Kevin Cross
Nice article, Jim!

For those looking for additional information on the topic, including a TechNet article, please consider reading my Converting Active Directory Timestamps in Microsoft SQL Server article.

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month