locked
Integer to Datetime RRS feed

  • Question

  • Hi

     

    I use this to get all users from active directory.

     

    SELECT *

    FROM openquery(

    adsi

    ,'SELECT name, AccountExpires FROM

    ''LDAP://company/OU=users,dc=company,dc=com''')

    WHERE AccountExpires IS NOT NULL AND AccountExpires not in('0','0x7FFFFFFFFFFFFFFF','9223372036854775807','')

     

    AccountExpires returns values like 128514708000000000 (This value represents the number of 100 nanosecond intervals since January 1, 1601 )

     

    How do I convert this value to Datetime?

     

    select getdate() returns a value like 2008-04-18 10:00:00.00 and that's how I'd like my AccountExpires

    Friday, April 18, 2008 12:36 PM

Answers

  • Adam,

     

    There is a slight flaw in your code sample, that makes a big difference in the result.  You made a years adjustment to the days value which skews the expiration out to 2306 wher it should be 2008.  It took me 2 hrs to figure out why your result wasn't matching mine in C#, before I caught this.  The corrected code would be something like:

     

    Code Snippet

    declare @LastLogin bigint

    set @LastLogin = 128514708000000000

    set @LastLogin = @LastLogin / (60 * 10000000)

    set @LastLogin = @LastLogin / 1440

    --set @LastLogin = @LastLogin -299

    set @LastLogin = @LastLogin - (datediff(dd, '1/1/1900', dateadd(yy, 299, '1/1/1900')))

    select dateadd(day,@lastlogin,'1/1/1900')

     

    -----------------------

    2008-03-30 00:00:00.000

    (1 row(s) affected)

     

     

    However, this is still a day off, likely due to a leap year difference.  I also redid the CLR function I provided earlier, to use a TimeSpan which is by default in 100ns increments.  This simplifys the function and ensures accuracy.

     

    Code Snippet

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlDateTime GetActiveDirectoryDate(long nanoseconds)

    {

    TimeSpan ts = new TimeSpan(nanoseconds);

    return (SqlDateTime)DateTime.Parse("01/01/1601").AddDays(ts.Days);

    }

     

     

     

    Code Snippet

    declare @size bigint

    set @size = 128514708000000000

    select dbo.GetActiveDirectoryDate(@size)

     

    -----------------------

    2008-03-31 00:00:00.000

    (1 row(s) affected)

     

     

     

    I don't know if the original poster cares about the missing day or not.  I'll leave it to them to decide.
    Friday, April 18, 2008 2:41 PM

All replies

  • This tells you how to make it a datetime value:  http://www.microsoft.com/technet/scriptcenter/topics/win2003/lastlogon.mspx
    Friday, April 18, 2008 12:59 PM
  • have you tried something like this...

     

    Code Snippet
    dateadd(ms,AccountExpires,'1601-01-01')

     

    Jon

     

    Friday, April 18, 2008 1:01 PM
  • Based on the link Shelly posted, you would have to do something like this.  You cannot do dateadd or datediff for the date 1/1/1601 because this date is invalid in SQL Server.  In SQL Server, the very first date is 1/1/1900.  To get this code to work we use the dateadd function from the date calculated from 1/1/1900 and subtract the difference between 1/1/1601 and 1/1/1900, which is 299.

     

    Code Snippet

    declare @LastLogin bigint

     

    set @LastLogin = 128514708000000000

    set @LastLogin = @LastLogin / (60 * 10000000)

    set @LastLogin = @LastLogin / 1440

     

    select dateadd(day,@lastlogin-299,'1/1/1900')

     

     

     

    Friday, April 18, 2008 1:37 PM
  •  

    Jon,

     

    Your Example is going to ecounter out of range values.  The dateadd function uses an integer value as the second input.  SQL also will not accept a value of 01/01/1601 for a datetime.  I resolved this with a CLR Function.

     

    Code Snippet

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    public partial class UserDefinedFunctions

    {

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlDateTime GetActiveDirectoryDate(double input)

    {

    return (SqlDateTime)DateTime.Parse("01/01/1601").AddMilliseconds(input);

    }

    };

     

     

    This function is a SAFE function so it should be simple to implement.  It gets around the out of range issues in SQL Server.

     

    Testing it returns:

     

    Code Snippet

    declare @size bigint

    set @size = 12851470800000

    select dbo.GetActiveDirectoryDate(@size)

     

    -----------------------

    2008-03-31 21:00:00.000

    (1 row(s) affected)

     

     

    Friday, April 18, 2008 1:39 PM
  • Adam,

     

    There is a slight flaw in your code sample, that makes a big difference in the result.  You made a years adjustment to the days value which skews the expiration out to 2306 wher it should be 2008.  It took me 2 hrs to figure out why your result wasn't matching mine in C#, before I caught this.  The corrected code would be something like:

     

    Code Snippet

    declare @LastLogin bigint

    set @LastLogin = 128514708000000000

    set @LastLogin = @LastLogin / (60 * 10000000)

    set @LastLogin = @LastLogin / 1440

    --set @LastLogin = @LastLogin -299

    set @LastLogin = @LastLogin - (datediff(dd, '1/1/1900', dateadd(yy, 299, '1/1/1900')))

    select dateadd(day,@lastlogin,'1/1/1900')

     

    -----------------------

    2008-03-30 00:00:00.000

    (1 row(s) affected)

     

     

    However, this is still a day off, likely due to a leap year difference.  I also redid the CLR function I provided earlier, to use a TimeSpan which is by default in 100ns increments.  This simplifys the function and ensures accuracy.

     

    Code Snippet

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlDateTime GetActiveDirectoryDate(long nanoseconds)

    {

    TimeSpan ts = new TimeSpan(nanoseconds);

    return (SqlDateTime)DateTime.Parse("01/01/1601").AddDays(ts.Days);

    }

     

     

     

    Code Snippet

    declare @size bigint

    set @size = 128514708000000000

    select dbo.GetActiveDirectoryDate(@size)

     

    -----------------------

    2008-03-31 00:00:00.000

    (1 row(s) affected)

     

     

     

    I don't know if the original poster cares about the missing day or not.  I'll leave it to them to decide.
    Friday, April 18, 2008 2:41 PM
  • I don't know if this is applicable to this situation but usually UNIX/Epoch dates stored as integers are in GMT and you have to account for that (GETUTCDATE() will help)

     

     

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, April 18, 2008 2:52 PM
  • Cool thanks for picking me up on that.  I couldnt tell if the date was right/wrong when I posted because I didnt do the math for to confirm the returned row. The logic seemed correct.  When I saw your post it made me think hmmm... why is my returned date, so much larger???

     

    Thanks for filling in the gaps.

    Friday, April 18, 2008 2:58 PM
  • Denis,

     

    I didn't consider this, but none of the online references for how to work with the AD data show this being done, so I am not sure if it is actually needed or not.  Since we are dealing with a day, of expiration, and not an hour or minute, I wouldn't think it would be necessary.

     

    Friday, April 18, 2008 3:20 PM
  • For all of the people interested in the Epoch dates please visit http://en.wikipedia.org/wiki/Epoch_(reference_date)

     

    Denis The SQL Menace

    http://sqlservercode.blogspot.com

    http://sqlblog.com/blogs/denis_gobo/default.aspx

    Friday, April 18, 2008 3:32 PM
  • Just what I was looking for: Thanks Jonathan
    Thursday, April 24, 2008 3:04 PM
  • I know this is an old article, but was an issue for me. Your wisdom helped me but unfortunately we will not be using SQLCLR...

    Fun with Math...  there is a hard number of nanoseconds between AD's '1601-01-01' and SQL's '1900-01-01'

    This hard value can be used as a placeholder or shortcut to the dateadd function.  Try this...

    declare @mydate bigint
    set @mydate = 130631832000000000
    select dateadd(mi,((@mydate - 94355064600000000) / 10000000 / 60), '1900-01-01')

    I verified in AD that this date is '2014-12-15 23:59', and that is what this returns. 

    How did I get here... Backwards

    select datediff(mi,'1900-01-01 00:00:00.000', '2014-12-15 23:59:59.999')
    --    60461279  minutes
    -- or 60461279 * 60 seconds
    -- or 3627676740 seconds
    -- or 36276767400000000 nanoseconds 
    -- so 130631832000000000 - 36276767400000000  should = '1900-01-01 00:00:00.000'
    --select 130631832000000000 - 36276767400000000  -- = 94355064600000000 = '1900-01-01 00:00:00.000'

    Just add your datediff to this in mi and you should get the correct date down to the minute.

    Wednesday, May 31, 2017 7:20 PM