Update Timestamp column with Active directory Timestamp


I have a table which has a column containing Active Directory timestamp. I have added a column in this table and trying to update it with datetime. I was trying the DATEADD in SSIS as well but that doesn't work either.

I am trying to use the following update command but it gives me the following error.

Ex: AD timestamp : 130153066505493000 converted timestamp: 2013-06-09 21:57:30.547

SQL statement:
UPDATE tablename
set [LastLogonTmstmp] = CONVERT(datetime,DATEADD(mi, -300, (LastLogonTmstmp_AD / 864000000000.0 - 109207)))

Msg 8115, Level 16, State 2, Line 26
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any help is much appreciated

That suggests that

(LastLogonTmstmp_AD / 864000000000.0 - 109207)

does not convert (implicitly) to DATETIME

Suggest you try explicitly converting it to DATETIME and then doing the DATEADD(Minute, -300, XXX) on the result

1 Like

Replace this part:

with this:

cast(lastlogontmstmp_ad/864000000000.0-109207.20833335 as datetime)

Thank you Kristen and bitsmed for your response. I tried both the options but I am still getting the same error. Did you try recreating this scenario and did it work for you?

To add, The following works perfectly fine in the SELECT statement but not UPDATE

SELECT TOP 1 DATEADD(mi, -300, (LastlogonTimestamp_AD / 864000000000.0 - 109207))
FROM table

SELECT TOP 1 DATEADD(mi, -300,cast(LastlogonTimestamp_AD/864000000000.0-109207 as datetime))
FROM table

SELECT TOP 1 cast(LastlogonTimestamp_AD/864000000000.0-109207.20833335 as datetime)
FROM table

The following worked. I had to change the column type as datetime2(7).

DATEADD(Minute,([LastLogonTimestamp_AD/ 600000000) + DATEDIFF(Minute,GetUTCDate(),GetDate()),CAST('1/1/1601' AS DATETIME2))

Yes tried with datetime and datetime2. Whats your table definition?