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
Change is inevitable... Change for the better is not.
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy