SQL Server Active Directory Import Issues with British Summer Time (BST)

Question

I'm using SQL Server ADSI to import data from Active Directory and using the whenChanged field to control if I need to reimport the members of a group.

Problem is the date is being returned in GMT and I'm currently in BST.

So, did a member change at 9:50 this morning, the AD tools I have say 9:50, but the ADSI query returns 8:50. This means if there are two changes within an hour I will miss one.

The query I am using looks like this:

Select distinguishedName, whenChanged 
From OpenQuery(ADSI, '<LDAP://OU=Groups,DC=mydomain,DC=adr>;(objectCategory=Group);distingusihedName, whenChanged')

Is there another field I could use that would give me an accurate date? Is there a better way to do what I am trying to do in SQL Server?

Answer 1

Why are you storing local time in the first place? The whenChanged property already gives you GMT, no hours can be "lost" in that time zone.

Its okay to convert to local time for display purposes, but I would definitely store GMT/UTC in the DB.