Convert LastLogonTimestamp to Date in Sql

The LastLogonTimeStamp attribute of the Active Directory stores the last logon timestamp value in the System.Int64 data type format. This timestamp is the number of 100-nanoseconds intervals since Jan 1, 1601, UTC.

The LastLogonTimeStamp is 18 digit active directory timestamp also named filetime. To make it in human-readable format, we have to convert lastlogontimestamp to date in SQL or PowerShell.

In this article, we will discuss how to convert lastlogontimestamp to date in SQL with the script. You will need to have SQL Server to run the SQL server script required for converting the 18 digits file time to date.

Convert LastLogonTimeStamp to datetime in SQL

We can convert lastlogontimetime or file time to date time in SQL server or in PowerShell to make it a human-readable format.

To convert the last logon timestamp to date in SQL, follow the below steps and command in SQL Server.

• Open the SQL Server management studio ( any version of SQL Server 2014 or SQL Server 2019 or later version)
• Declare bigint variable which stores the 18 digits lastlogontimestamp value.
• Use the select statement to cast bigint 18 digits file time to date in SQL.
• Copy the below script and execute it to convert lastlogontimestamp to date in SQL.

declare @lastlogontime bigint
set @lastlogontime = 132975302840000000
Select CAST((convert(bigint, @lastlogontime) / 864000000000.0 - 109207) AS DATETIME) as LastLogonTimestamp

The output of the above SQL Script to convert the lastlogontime to date in the SQL server and in human-readable format is:

LastLogonTimestamp
2022-05-20 14:24:44.000

Cool Tip: Do you know the difference between lastlogon vs lastLogonTimeStamp vs lastLogonDate!

Conclusion

I hope the above article on how to convert lastLogonTimeStamp to date time in SQL is helpful to you.

You can also convert lastlogontimestamp to date time in PowerShell using the DateTime class. DateTime class has FromFileTime method which accepts 18 digits file time and returns the date-time in a human-readable format.

You can find more topics about PowerShell Active Directory commands and PowerShell basics on the ShellGeek home page.