Converting EPOCH time as a usable date for comparing to current datetime in SQL Server-CodePudding

Data value example, column [last_run] value 1647644503000000000

SELECT
    CAST(DATEADD(s, (last_run / 1000000000) - 18000, '19700101') AS DATETIME2) AS 'Last_Backup'

This converts it properly to Last_Backup = 2022-03-18 18:01:43.0000000

When I try to add this query

, DATEDIFF(DAY, 'Last_Backup', GETDATE()) AS 'DateDifference'

I get the following error:

Conversion failed when converting date and/or time from character string.

How do I avoid this error?

CodePudding user response：

While not the most efficient, you could always just repeat the computation like this:

SELECT
    CAST(DATEADD(s, (last_run / 1000000000) - 18000, '19700101') AS DATETIME2) AS 'Last_Backup',
    DATEDIFF(DAY, CAST(DATEADD(s, (last_run / 1000000000) - 18000, '19700101') AS DATETIME2), GETDATE()) AS 'DateDifference'