0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Falleitao
  • 1
  • 1
    "I have a SQL table with a column in varchar format that gives me dates in timestamp format." - why aren't you using correctly-typed columns? – Dai Mar 28 '22 at 04:03
  • Does this answer your question? [Convert .NET Ticks to SQL Server DateTime](https://stackoverflow.com/questions/2313236/convert-net-ticks-to-sql-server-datetime) – zedfoxus Mar 28 '22 at 04:55

1 Answers1

0

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
Dai
  • 141,631
  • 28
  • 261
  • 374
  • Still in error. Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. – Falleitao Mar 28 '22 at 04:22