August 7, 2008 at 9:16 am
I have an ldap query which writes data to a SQL table. Fields like "LastLoggedON". Unfortunately it extracts this data in 100 Nanoseconds since 1/1/1601. I have been unsuccessful in converting these large integers. Example: this number reflects someone who logged in at approximately noon yesterday. (128624995457225598)
I have this correct syntax for Dateadd but when the integer gets too big it throws this error:
Arithmetic overflow error converting expression to data type int.
select dateAdd(hour,
datediff(hour, getutcdate(), getdate()), --UTC offset
dateadd(second, 1116012701, '1/1/1970 12:00 AM'))
August 7, 2008 at 10:08 am
You are going to have to do some interesting work to get this up because the Datetime datatype starts at 1/1/1753. Here is how I think you need to do it.
[font="Courier New"]SELECT
128624995457225598*100/POWER(10, 9) seconds_since_1601,
CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60 seconds_in_153_years,
128624995457225598*100/POWER(10, 9) - (CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60) col1_minus_col2,
(128624995457225598*100/POWER(10, 9) - (CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60))/60 minutes,
DATEADD(minute,(128624995457225598*100/POWER(10, 9) - (CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60))/60, '1/1/1753') AS date[/font]
I think this will do what you need.
Jack Corbett
Consultant - Straight Path Solutions
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
August 7, 2008 at 10:46 am
This works GREAT: (your code)
SELECT
DATEADD(minute,(128624995457225598*100/POWER(10, 9) - (CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60))/60, '1/1/1753') AS date
But as soon as I try to plug in the real column (which had to be imported as a varchar) I get this error:
Arithmetic overflow error converting expression to data type bigint.
SELECT
DATEADD(minute,(convert(bigint,lastlogon)*100/POWER(10, 9) - (CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60))/60, '1/1/1753') AS date
from dbo.smx_empdirectory
August 7, 2008 at 11:04 am
Sounds like you have a really huge number on your hands then, or you have some non-numeric data in that column. I'd usually try to break the problem down into chunks here. I'd start by doing:
[font="Courier New"]SELECT
lastlogon
FROM
TABLE
WHERE
ISNUMERIC(lastlogon) <> 1[/font]
To see if I have any non-numeric characters although this doesn't always work as '+', '-', and '.' will return 1.
Jack Corbett
Consultant - Straight Path Solutions
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
August 7, 2008 at 11:23 am
Good thinking. There were no non-numerics except what appeared to be white space. I deleted all those rows and searched for + and -. I also found some ZERO's and deleted those rows.
I did a max and min len and all the integers are 18 in length.
Still get the same error.
August 7, 2008 at 11:27 am
Here's what I'd do next (assumes you have primary key on the source table):
[font="Courier New"]CREATE TABLE logins
(
pk datatype,
lastlogon bigint
)
INSERT INTO logins
SELECT TOP 50%
A.pk,
A.laslogon
FROM
TABLE A LEFT JOIN
logins L ON
A.pk = L.pk
WHERE
L.pk IS NULL
SELECT
lastlogon
FROM
TABLE
WHERE
ISNUMERIC(lastlogon) <> 0[/font]
Jack Corbett
Consultant - Straight Path Solutions
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
August 7, 2008 at 11:35 am
Not sure, but would using a decimal (decimal(24,0)) instead of bigint help at all?
😎
August 7, 2008 at 12:07 pm
Actually, I think that this will fix Jack's original code:
SELECT
DATEADD(minute,(convert(bigint,lastlogon)*(100/POWER(10, 9)) - (CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60))/60, '1/1/1753') AS date
from dbo.smx_empdirectory
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 7, 2008 at 12:15 pm
I don't see any change....
August 7, 2008 at 12:45 pm
Actually, it's a different error now. This should do it:
SELECT
DATEADD(minute,(convert(bigint,lastlogon)*100.0/POWER(10, 9) - (CONVERT(bigint, DATEDIFF(minute, '1/1/1801', '1/1/1953')) * 60))/60, '1/1/1753') AS date
from dbo.smx_empdirectory
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 7, 2008 at 12:59 pm
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
Consultant - Straight Path Solutions
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
August 7, 2008 at 9:15 pm
You mean to tell me that LDAP queries can't change the format of the date?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2013 at 7:03 pm
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
Change is inevitable... Change for the better is not.
December 5, 2014 at 3:29 am
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 [font="Courier New"]w32tm.exe /ntte 129941783963332926[/font] 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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply