SQL Server T-Sql Convert filetime to datetime

Question

I need to convert a filetime field value to datetime format through t-sql in SQL Server

I have applied following formula to do so which works absolutely fine for positive values (>0). But somehow this formula does not work for negative values and it return null value.

SELECT LastModifiedTime,
       CASE WHEN LastModifiedTime <= 1 THEN NULL
       ELSE DATEADD(ms,
                    ((LastModifiedTime) / CAST(10000 AS bigint)) % 86400000,
                    DATEADD(day, (LastModifiedTime) / CAST(864000000000 AS bigint) - 109207, 0))
       END AS Expr1
FROM TableName

Sample value i had tried were:

• -9094352270227314699 (negative value which did not work)
• 129046581660000000 (positive value which works)

Answer 1

CASE WHEN LastModifiedTime <= 1 THEN NULL

It's doing what you told it to do.