Sql-server – How to convert Extended Event action mdmget_TimeStampUTC to datetime

extended-eventssql server

SQL Server Extended Events can include an action of mdmtargetpkg.mdmget_TimeStampUTC. It returns a value such as 132085458320550473. How can that be converted to an actual date?

It doesn't appear to be a valid unix timestamp, even using microseconds.

Maybe it's the TimeStamp is actually RowVersion issue– but this explicitly has "UTC" in it, implying it's a real time.

@@VERSION:

Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Jul  3 2019 10:02:53 
    Copyright (C) 2019 Microsoft Corporation

Best Answer

The value 132085458320550473 is the number of ticks since January 1, 1601. Use the below function to convert it to datetime:

CREATE FUNCTION dbo.ConvertTicksToDateTime ( @Ticks bigint )
  RETURNS datetime
AS
BEGIN
    DECLARE @DateTime datetime2 = '16010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END
Related Question