help converting filetime bigint to SQL datetime

Hello,
I am using a DTS package to putt information from a Windows 2003 Active
Directory into SQL 2000.
The DTS package connects to AD, which is set up as a linked server, and
reads several attributes of user accounts, including the accountExpires and
lastLogonTimestamp columns.
The accountExpires and lastLogonTimestamp fields store dates and times. I
found an excellent technet article by the Scripting Guys that explains that
this value is stored in AD as a 64-bit integer, and that it represents the
number of 100-nanosecond units since January 1, 1601.
Unfortunately, SQL Server doesn't store dates that way. The datetime type in
SQL server is the number of three-hundredths of a second since 01/01/1753.
I thought about writing a conversion function, but I realized two things: 1)
someone must have encountered this before, and 2) it's not as easy as it
might seem at first glance because of leap years.
Moreover, I can't do anything about it in the DTS package with a custom
ActiveX Transformation because (I think) the ActiveX implementation in DTS
only handles 32-bit integers.
Can anyone help me with this? I really need to store those two columns in
SQL Server.
Thank you!
- Michael GibbonsHi Michael
You could try subtracting the difference (in 100 ns units) between the two
base dates, convert the remainder to nano-seconds and then adding to the SQL
Server base date.
John
"Michael" wrote:

> Hello,
> I am using a DTS package to putt information from a Windows 2003 Active
> Directory into SQL 2000.
> The DTS package connects to AD, which is set up as a linked server, and
> reads several attributes of user accounts, including the accountExpires an
d
> lastLogonTimestamp columns.
> The accountExpires and lastLogonTimestamp fields store dates and times. I
> found an excellent technet article by the Scripting Guys that explains tha
t
> this value is stored in AD as a 64-bit integer, and that it represents the
> number of 100-nanosecond units since January 1, 1601.
> Unfortunately, SQL Server doesn't store dates that way. The datetime type
in
> SQL server is the number of three-hundredths of a second since 01/01/1753.
> I thought about writing a conversion function, but I realized two things:
1)
> someone must have encountered this before, and 2) it's not as easy as it
> might seem at first glance because of leap years.
> Moreover, I can't do anything about it in the DTS package with a custom
> ActiveX Transformation because (I think) the ActiveX implementation in DTS
> only handles 32-bit integers.
> Can anyone help me with this? I really need to store those two columns in
> SQL Server.
> Thank you!
> - Michael Gibbons