'SQL Convert int64/Time to Date

I have a SQL table with a column of varchar datatype that contains dates in timestamp format.

AccessId    Date                 ComputerName   NumberOfTries
-------------------------------------------------------------
    1       132926085611026702   TESTE                1

When I use the command

[datetime]::FromFileTime('132926085611026702')

in PowerShell, I can get properly the data:

Thursday, March 3, 2022 4:03:42 PM

But I would like to know how I do this direct conversion through SQL to use in Power BI

sql


Solution 1:[1]

Given that DateTime.FromFileTime(Int64) handles 1601-01-01 00:00:00Z-based values....

A Windows file time is a 64-bit value that represents the number of 100-nanosecond intervals that have elapsed since 12:00 midnight, January 1, 1601 A.D. (C.E.) Coordinated Universal Time (UTC).

...then you need to use CONVERT( bigint, "Date" ), and then pass that into DATEADD with nanosecond and '1601-01-01 00:00:00':

SELECT
    DATEADD( nanosecond, CONVERT( bigint, "Date" ), CONVERT( datetime2(7), '1601-01-01 00:00:00' ) )
FROM
    myTable

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions