How to get last modified time of a document in sharepoint?

Question

How to convert LastModifiedTime value of MssCrawlUrl table ?

I tried below query,

Select CAST(LastModifiedTime as datetime)
From MssCrawlUrl Where DisplayURL like '<DisplayURL>'

But it is giving an error

'Arithmetic overflow error converting expression to data type datetime.'

Answer 1

It is not supported to query the SharePoint databases directly. You must use the API. Apart from the official "not supported" reason, it can cause locking, return incorrect data, and custom queries may break between service packs or product versions. See Mike Fitz's post Please Stay Out Of The Database! from his time at Microsoft.

See this Visual How To for an example of how to use the supported API. Also see these articles on MSDN and this best practices article for further information.

Answer 2

I got the ans

DECLARE @dt AS bigint
SET @dt = <BIGINT>
SELECT DATEADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
DATEADD(day, @dt / CAST(864000000000 AS bigint) - 109207, 0))