Collecting Active Directory dates

[Ross Luker]

Hi,

I've just started using ADSI to pull in info from our Active Directory
tree into SQL Server 2000. I've made the link ok, and can pull in most
fields (cn whenCreated etc) fine. However, there are some date fields
(the one I'm interested in is pwdLastSet) that are represented as a
long numeric string, which throws up an error when SQL tries to pull it
in. Is there an easy way to parse these fields into a standard
datetime field, or if not how do I force SQL to pull the numeric field
in, and convert it later?

TIA,
Ross

[Simon Hayes]

Re: Collecting Active Directory dates


"Ross Luker" <ross_luker@hot mail.com> wrote in message
news:1104940631 .745926.294600@ c13g2000cwb.goo glegroups.com.. .[color=blue]
> Hi,
>
> I've just started using ADSI to pull in info from our Active Directory
> tree into SQL Server 2000. I've made the link ok, and can pull in most
> fields (cn whenCreated etc) fine. However, there are some date fields
> (the one I'm interested in is pwdLastSet) that are represented as a
> long numeric string, which throws up an error when SQL tries to pull it
> in. Is there an easy way to parse these fields into a standard
> datetime field, or if not how do I force SQL to pull the numeric field
> in, and convert it later?
>
> TIA,
> Ross
>[/color]

It would probably be a good idea to give an example of one of the numeric
strings, and the date it represents - personally, I'm not at all familiar
with ADSI, although others here may be. I'm also not sure how you're pulling
the data - if you're using DTS, you could use a custom ActiveX
transformation, if the existing date transformation won't handle it; if
you're using a linked server, then a UDF might be one solution.

Simon

[Ross Luker]

Re: Collecting Active Directory dates

Hi Simon,

If I look at AD data using the windows LDIFDE tool, there are some
fields such as the one below, which are retrieved ok:
whenChanged: 20041202105508. 0Z - MSSQL formats this fine to 02/12/2004,
10:55

However, most of the date/time fields are in the format:
pwdLastSet: 127463655814071 600
which I'm guessing is a counter in (maybe) seconds from some date, but
I can't find any info to prove this! Trying to pull this field in (I'm
using a linked server) results in an error "Could not convert the data
value due to reasons other than sign mismatch or overflow". As I said,
if I knew more about what the data in the fields are, I might be able
to work on transforming it!

Ross

[Simon Hayes]

Re: Collecting Active Directory dates


"Ross Luker" <ross_luker@hot mail.com> wrote in message
news:1104943553 .231483.173640@ c13g2000cwb.goo glegroups.com.. .[color=blue]
> Hi Simon,
>
> If I look at AD data using the windows LDIFDE tool, there are some
> fields such as the one below, which are retrieved ok:
> whenChanged: 20041202105508. 0Z - MSSQL formats this fine to 02/12/2004,
> 10:55
>
> However, most of the date/time fields are in the format:
> pwdLastSet: 127463655814071 600
> which I'm guessing is a counter in (maybe) seconds from some date, but
> I can't find any info to prove this! Trying to pull this field in (I'm
> using a linked server) results in an error "Could not convert the data
> value due to reasons other than sign mismatch or overflow". As I said,
> if I knew more about what the data in the fields are, I might be able
> to work on transforming it!
>
> Ross
>[/color]

It looks unlikely to be seconds since an epoch, since the number above would
be more than 4 billion years (I think - very quick calculation). You should
probably follow up on the ADSI side - in an AD newsgroup, perhaps - to find
out what the number represents.

Until you find out more details, you could use ISDATE() to put in a null (or
something else) for your import - it's not always reliable, but in this case
it should be OK:

select cast(case when isdate(pwdLastS et) = 0 then null else pwdLastSet end
as datetime) as pwdLastSet
from ADSI..LinkedTab le

Simon