Daylight Savings Handling in DateDiff() in MS Access?

Question

I am fully aware of DateDiff()'s inability to handle daylight savings issues. Since I often use it to compare the number of hours or days between 2 datetimes several months apart, I need to write up a solution to handle DST. This is what I came up with, a function that first subtracts 60 minutes from a datetime value if it falls within the date ranges specified in a local table (LU_DST). Thus, the usage would be:

 datediff("n",Conv_DST_to_Local([date1]),Conv_DST_to_Local([date2]))

My question is: Is there a better way to handle this? I'm going to make a wild guess that I'm not the first person with this question. This seems like the kind of thing that should have been added to one of the core reference libraries. Is there a way for me to access my system clock to ask it if DST was in effect at a certain date & time?

 Function Conv_DST_to_Local(X As Date) As Date
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("LU_DST")
    Conv_DST_to_Local = X
    While rst.EOF = False
        If X > rst.Fields(0) And X < rst.Fields(1) Then Conv_DST_to_Local = DateAdd("n", -60, X)
        rst.MoveNext
    Wend
End Function

Notes

1. I have visited and imported the BAS file of http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx. I spent at least an hour by now reading through it and, while it may do its job well, I can't figure out how to modify it to my needs. But if you have an answer using his data structures, I'll take a look.
2. Timezones are not an issue since this is all local time.

Edit

Thanks for solutions, HansUp and Fenton, but I may be seeing a bug in the WbemScripting:

 Debug.Print Conv_date_to_Utc(#11/07/2009 02:15 PM#)

returns 11/7/2009 6:15:00 PM

 Debug.Print Conv_date_to_Utc(#11/08/2009 02:15 PM#)

returns 11/8/2009 7:15:00 PM

(I live in GMT-05:00). Every website I can find is saying that the DST switch in November 2009 was on the 1st, not the 7th. Why is this saying otherwise?

Answer 1

Warning: The method below does not return correct results for Daylight Saving Time transitions in U.S. for 2009.

Consider converting your date/time values to UTC before feeding them to DateDiff()

Public Function VT_DATE_to_UTC(ByVal pDate As Date) As Date
    Dim odateTime As Object
    Set odateTime = CreateObject("WbemScripting.SWbemDateTime")

    odateTime.SetVarDate pDate, True

    VT_DATE_to_UTC = odateTime.GetVarDate(False)
    Set odateTime = Nothing
End Function

An example in the Immediate Window:

? DateDiff("n", #2010/03/14 01:00#, #2010/03/14 03:00#)
 120 

? DateDiff("n", VT_DATE_to_UTC(#2010/03/14 01:00#), _
    VT_DATE_to_UTC(#2010/03/14 03:00#))
 60 

Answer 2

This is a refinement of @HansUp's solution, but more efficient if you call it multiple times, as it doesn't re-initialize and close the outside library each time it's called:

  Public Function VT_DATE_to_UTC(ByVal pDate As Date, _
       Optional bolCleanup As Boolean) As Date
    Static odateTime As Object

    If odateTime Is Nothing Then
       Set odateTime = CreateObject("WbemScripting.SWbemDateTime")
    ElseIf bolCleanup
       Set odateTime = Nothing
       Exit Function
    End If
    odateTime.SetVarDate pDate, True
    VT_DATE_to_UTC = odateTime.GetVarDate(False)
  End Function

This is will initialize the static variable the first time it's called and then re-use that object each time it's called until you call it with the optional argument set to True, which will close the external object.

Should you be using this object in more than one function, it would make more sense to define a standalone function that returns the object, and use that, instead:

  Public Function odateTime(Optional bolCleanup As Boolean) As Object
    Static objDateTime As Object

    If objDateTime Is Nothing Then
       Set objDateTime = CreateObject("WbemScripting.SWbemDateTime")
    ElseIf bolCleanup
       Set objDateTime = Nothing
       Exit Function
    End If
    Set odateTime = objDateTime 
  End Function

  Public Function VT_DATE_to_UTC(ByVal pDate As Date) As Date
    odateTime.SetVarDate pDate, True
    VT_DATE_to_UTC = odateTime.GetVarDate(False)
  End Function

With this code, the first time you call the UTC function, it will initialize the date/time object (if it's not already initialized). And you'd clean up the object in your app's shutdown routine, with Call odateTime(True).

Answer 3

(Original Poster here)

Thanks for the help, HansUp and Fenton. I ended up using both of your suggestions to make a function that relies on Lotus Notes to convert to GMT for me.

Naturally, it's almost as bugged as the wbemscripting API thingy, but I made a workaround for one bug and the other isn't relevant (bugged for dates 2006 and earlier).

'Note: 1 minute is added to the NtDt to ensure the time component is created.  It is later subtracted.
Function ToGMT(ByVal X As Date) As Date
   Static NtSession As NotesSession
   If NtSession Is Nothing Then
       Set NtSession = New NotesSession
       NtSession.Initialize
   End If

   'Adjust for midnight
   Dim MidAdj As Boolean
   If TimeValue(X) = "12:00:00 AM" Then
       X = DateAdd("n", 1, X)
       MidAdj = vbTrue
   End If

   Dim NtDt As New NotesDateTime
   Set NtDt = NtSession.CreateDateTime(X)
   NtDt.ConvertToZone 5, True
   If MidAdj Then
      ToGMT = DateAdd("n", -1, CDate(NtDt.LSGMTTime))
   Else
      ToGMT = CDate(NtDt.LSGMTTime)
   End If
End Function