Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Convert nanoseconds since 1/1/1601 Expand / Collapse
Author
Message
Posted Thursday, August 7, 2008 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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 Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check 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 help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #548601
Posted Thursday, August 7, 2008 9:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
You mean to tell me that LDAP queries can't change the format of the date?

--Jeff Moden
"RBAR 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #548834
Posted Friday, December 13, 2013 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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?


--Jeff Moden
"RBAR 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522915
Posted Friday, December 5, 2014 3:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:13 AM
Points: 503, Visits: 11,153
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 ##years
SELECT
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 year
http://support.microsoft.com/kb/214019

How to convert date/time attributes in Active Directory to standard time format
https://support.microsoft.com/kb/555936?
Post #1641761
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse