SQLTeam.com | Weblogs | Forums

Update Timestamp column with Active directory Timestamp


#1

Hello,

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)))

Error:
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


#2

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


#3

Replace this part:

with this:

cast(lastlogontmstmp_ad/864000000000.0-109207.20833335 as datetime)

#4

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?


#5

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


#6

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))


#7

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