December 13, 2011 at 4:09 pm
Hello,
I have an LDAP query that gets some users. This works great. I also filter to make sure I am not grabbing users that are disabled or have never logged on. All good so far. I also want to exclude stale accounts. To us stale account are ones that have not logged in for over 6 months.
The problem comes in that I need to pass an Integer8 number to the LDAP / ADSI query. Now the math for that is easy as seen below. (@ConversionDate = DATETIME)
SELECT DATEADD(mi, (@ConversionDate * 600000000) + 157258080), 0)
The problem is mixing apples and oranges. In other words a DateTime format with integer math. So I want to convert the Date passed in to an BIGINT then do the math.
Now I have a get out of jail free card in that I can if needed do the conversion in C# so the SP gets an integer.....but I'd like to keep as much database side as possible.
So is there a function to convert a date time to and integer given an epoch date or do I need to write that as well?
TIA
JB
December 14, 2011 at 2:08 pm
You can just do a convert(INT, datetime_value) and it returns the # of days from 1900-01-01. Of course it also does rounding do when you get to around noon it gives you an extra day.
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
December 14, 2011 at 7:53 pm
nfs_john (12/13/2011)
So is there a function to convert a date time to and integer given an epoch date or do I need to write that as well?
Which epoch date and unit of measurement do you want? Or do you just want the date to be converted to a number like 20110101?
--Jeff Moden
December 27, 2011 at 5:05 am
OK so I have been off on another fire. Back to this one now. What I need to do is have a stored procedure that will take a date (12-12-2011 00:00:00.000) etc. and turn it into the NT integer8 time format. For some reason Microsoft has two differing epoch dates. For SQL 1-1-1901 and for NT 1-1-1601. Finding no function(s) for converting to and fro I wrote one for our SQL ADSI queries which use the lastLogonTimestamp to find stale accounts on an NT domain with over 17K accounts.
To convert a date to NTTIME Integer8.
SELECT dbo.sfn_ConvertDateTimeInteger8('12-12-2011')
129681576720864000
SELECT dbo.sfn_ConvertInteger8DateTime(129681576720864000)
2011-12-12 00:00:00.000
CREATE FUNCTION [dbo].[sfn_ConvertDateTimeInteger8]
(
@ConversionDate DATETIME
)
RETURNS NUMERIC(38,0)
AS
BEGIN
--This will hold the number of days from 1-1-1901 to whatever day passed in
DECLARE @NumDaysConvDate BIGINT
--There are 86400000000000 nanoseconds in a day but Integer8 is 100 nanosecond units so divide by 100
DECLARE @NanoSecondsDay BIGINT
SET @NanoSecondsDay = 864000000000
--This will be the # days from 1-1-1901 multiplied to get 100 nanosecond units
DECLARE @ConvDateNanoSeconds NUMERIC(18,0)
--Number of Nanosecond units between two epoch dates 1-1-1601 & 1-1-1901 (299 Years * 365.242199 days in a year) * 864000000000 (nanosecond units in a day)
DECLARE @EpochDatesNanoSeconds NUMERIC(18,0)
SET @EpochDatesNanoSeconds = 94355208720864000
--First we will convert our date passed in to number of days from SQL epoch date
SET @NumDaysConvDate = CONVERT(BIGINT, @ConversionDate)
--SELECT @NumDaysConvDate AS '@NumDaysConvDate'
--Second we will calculate the number of 100 nanosecond units that is equal to days since SQL epoch
SET @ConvDateNanoSeconds = @NumDaysConvDate * @NanoSecondsDay
--SELECT @ConvDateNanoSeconds AS '@ConvDateNanoSeconds'
RETURN
(
--Finally for some reason there are differing epoch dates in SQL and Integer8 (Active Directory)
--So we add the nanosecond units from from above to a precalculated quantity which is the delta of 1-1-1601 to 1-1-1901
SELECT @ConvDateNanoSeconds + @EpochDatesNanoSeconds
--YEAH I know not 100% accurate to the second. Doesn't need to be for our use.
)
END
GO
AND just in case we ever need to convert from Integer8 back to DateTime.
CREATE FUNCTION [dbo].[sfn_ConvertInteger8DateTime]
(
@ConversionInt8 NUMERIC(38,0)
)
RETURNS DATETIME
AS
BEGIN
--This will hold the number of days from 1-1-1901 to whatever day passed in
DECLARE @NumDaysConvDate BIGINT
--This will be the # days from 1-1-1901 multiplied to get 100 nanosecond units
DECLARE @ConvDateNanoSeconds NUMERIC(18,0)
--There are 86400000000000 nanoseconds in a day but Integer8 is 100 nanosecond units so divide by 100
DECLARE @NanoSecondsDay BIGINT
SET @NanoSecondsDay = 864000000000
--Number of Nanosecond units between two epoch dates 1-1-1601 & 1-1-1901 (299 Years * 365.242199 days in a year) * 864000000000 (nanosecond units in a day)
DECLARE @EpochDatesNanoSeconds NUMERIC(18,0)
SET @EpochDatesNanoSeconds = 94355208720864000
--First for some reason there are differing epoch dates in SQL and Integer8 (Active Directory)
--So we subtract the nanosecond units from from above to a precalculated quantity which is the delta of 1-1-1601 to 1-1-1901
--This will "jump us forward in time to 1-1-1901
SET @ConvDateNanoSeconds = @ConversionInt8 - @EpochDatesNanoSeconds
--Second we will calculate the number of 100 nanosecond units that is equal to days since SQL epoch
--This will tell us how many complete days have elapsed since 1-1-1901
SET @NumDaysConvDate = @ConvDateNanoSeconds / @NanoSecondsDay
--SELECT @ConvDateNanoSeconds AS '@ConvDateNanoSeconds'
RETURN
(
--Finally we will convert our number days since 1-1-1901 into a SQL Date time.
SELECT CONVERT(DATETIME, @NumDaysConvDate)
--YEAH I know not 100% accurate to the second. Doesn't need to be for our use.
)
END
GO
And just for giggles if anyone cares here is how it is used in a sp as part of the ADSI query.
....
DECLARE @NTInt8Time NUMERIC(38,0)
......
SELECT @NTInt8Time = dbo.sfn_ConvertDateTimeInteger8(@LastLogonDate)
--Now concat AD query BTW we don't concat common name as that is returned as part of the full dn
SET @str_LDAP = '''' + @ADServer + ';(&((objectCategory=' + @objCat + ')(SAMAccountName=' + @PageChar + '*)(SAMAccountName=' + @PageChar + '*)(!lastLogonTimeStamp=NULL)(lastLogonTimeStamp>= '+ CONVERT(VARCHAR(100), @NTInt8Time) + '))(memberOf:1.2.840.113556.1.4.1941:=' + @DNPath + '));' + @objAD_attributes + ''''
--PRINT @str_LDAP
.....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply