## Convert nanoseconds since 1/1/1601

 Author Message Jack Corbett SSC-Forever Group: General Forum Members Points: 42593 Visits: 14925 Actually Lynn has it right. You can't directly convert the lastlogon to bigint because it is larger than bigint, so you need to convert lastlogon to a larger decimal like decimal(24,0), then do the division to convert to minutes and convert to bigint. Jack CorbettApplications Developer Don't let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck out these links on how to get faster and more accurate answers: Forum Etiquette: How to post data/code on a forum to get the best helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance ProblemsCrosstabs and Pivots or How to turn rows into columns Part 1Crosstabs and Pivots or How to turn rows into columns Part 2 Jeff Moden SSC Guru Group: General Forum Members Points: 206043 Visits: 41952 You mean to tell me that LDAP queries can't change the format of the date? --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Jeff Moden SSC Guru Group: General Forum Members Points: 206043 Visits: 41952 Jeff Moden (8/7/2008)You mean to tell me that LDAP queries can't change the format of the date?BWAAA-HAAA!!!! I guess a more than 5 year wait for an answer means, "No", huh? :-D --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs tripleAxe SSCrazy Group: General Forum Members Points: 2044 Visits: 13541 If you know how many 100-nanosecond intervals (ticks) are in a day (864000000000) and how many days difference there are between 1-1-1601 and 1-1-1900 which is SQL Servers base date (109207) this query gets a little bit easier. Divide the value you get from the Active Directory query by the number of ticks in a day to get the number of days since 1-1-1601, then take away the number of days between 1-1-1601 and 1-1-1900 and the convert to a datetime. For example, say my AD query returned the value 129941783963332926 for one of my logins run this query below to return the value "2012-10-08 13:59:56.330"`SELECT CONVERT(DATETIME, (129941783963332926 / 864000000000)- 109207 )`You can check this converts to the correct value using the Windows Time Service command line utility. e.g run w32tm.exe /ntte 129941783963332926 from a command prompt.Here is how to do the conversion using a table.`SET NOCOUNT ON;IF OBJECT_ID('tempdb..##logon','U') IS NOT NULL DROP TABLE ##logon;CREATE TABLE ##logon (last_logon BIGINT);INSERT INTO ##logon (last_logon) VALUES ('130606598434647275');INSERT INTO ##logon (last_logon) VALUES ('130619908502831361');INSERT INTO ##logon (last_logon) VALUES ('129941783963332926');INSERT INTO ##logon (last_logon) VALUES ('130621100686409404');INSERT INTO ##logon (last_logon) VALUES ('130621789182632438');INSERT INTO ##logon (last_logon) VALUES ('130621388365566800');SET NOCOUNT OFF;SELECT last_logon , CONVERT(DATETIME, (last_logon / 864000000000)- 109207 )FROM ##logon;DROP TABLE ##logon;`Here is how I calculated the number of days between 1-1-1601 and 1-1-1900 (of course I could have used a newer version of SQL instead of 2005 and the datetime2 datatype to work this out).`IF OBJECT_ID('tempdb..##years','U') IS NOT NULL DROP TABLE ##years;CREATE TABLE ##years(TheYear smallint, DayCount smallint);WITH YearsCTE (theyear)AS( SELECT theyear = 1601 UNION ALL SELECT theyear = theyear + 1 FROM YearsCTE WHERE theyear < 1899 )INSERT INTO ##yearsSELECT theyear , CASE WHEN (theyear % 4) <> 0 THEN 365 WHEN (theyear % 4) = 0 AND (theyear % 100 <> 0) THEN 366 WHEN (theyear % 4) = 0 AND (theyear % 100 = 0 ) AND (theyear % 400 = 0) THEN 366 ELSE 365 END AS [Days]FROM YearsCTE OPTION (maxrecursion 0);select SUM(DayCount) FROM ##years`If you have a newer version of SQL you could run this:`DECLARE @date1 DATETIME2 = '16010101'DECLARE @date2 DATETIME2 = '19000101'SELECT DATEDIFF(DAY,@date1, @date2)`Some URLs I found useful for this are below.Method to determine whether a year is a leap yearhttp://support.microsoft.com/kb/214019How to convert date/time attributes in Active Directory to standard time formathttps://support.microsoft.com/kb/555936?