How to Convert FileTime to DateTime

One of my customers recently wanted to rename each of the SQL audit files will the datetime stamp of when it was created. I explained to them the filename already contains a datetime stamp. While it does not look like a typical timestamp, it is based on the Windows Filetime data structure that is a 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (UTC). Nonetheless, they still wanted a traditional datetime stamp in the file name.

According to the documentation, the audit file naming convention is this:

<Audit Name>_<Audit GUID>_<Sequential partition number>_<Timestamp>.sqlaudit

and if we use this file as an example:

SERVER_AUDIT_BA244465-88A7-4752-8B50-4970F2A270AA_0_133740732445520000.sqlaudit

then we know the value at the end, 133740732445520000, is the datetime stamp.

We can use the following T-SQL code to convert that value to a datetime2 data type 2024-10-22 12:20:44.5520000 UTC.

DECLARE @FileTime BIGINT = 133740732445520000

SELECT
	DATEADD(
		MILLISECOND,
		@FileTime%10000000/10000,
		DATEADD(
			SECOND,
			(@FileTime-125911584000000000)/10000000, 
			CAST('2000-01-01' AS DATETIME2)
		)
	) AS DateTimeUTC
GO

T-SQL script to convert Filetime to Datetime

Filetime is stored as UTC, but we can easily convert it to our local system time using this query.

DECLARE @DateTimeUTC DATETIME2 = '2024-10-22 12:20:44.5520000'

SELECT 
	CONVERT(
		DATETIME2,
		SWITCHOFFSET(
			CONVERT(DATETIMEOFFSET, @DateTimeUTC), DATENAME(TZOFFSET, SYSDATETIMEOFFSET())
		)
	)
GO

My system time is set to Eastern Time Zone, so we get the following result.

T-SQL script to convert Datetime from UTC to local system time

Converting the Windows Filetime to datetime using T-SQL does take a few steps; however, we can do that with a single line of code in PowerShell.

[System.DateTime]::FromFileTimeUtc(133740732445520000)

And if we want to get the local system time, we can use this statement.

[System.DateTime]::FromFileTime(133740732445520000)

PowerShell showing FromFileTime() and FromFileTimeUtc() functions

So back to our original problem of renaming the files. We have the following files that need to be renamed.

Windows File Manager showing several audit files

We can run the following PowerShell script to convert the Windows Filetime and append it to the end of the file name.

Get-ChildItem -Path "M:\Audit" | ForEach-Object {
    $filetime = $_.BaseName.Split('_')[-1]
    $datetime = $([System.DateTime]::FromFileTimeUtc($filetime).ToString('yyyyMMdd-HHmmss-fffffff'))
    Rename-Item -Path $_.FullName -NewName "$($_.BaseName)_$($datetime)$($_.Extension)"
}

You can see the additional timestamp added to the end of the file name.