Daylight saving time in DateDiff () in MS Access?

Question

I am fully aware of the inability of DateDiff () to cope with day saving issues. Since I often use it to compare the number of hours or days between two dates between several months, I need to write a solution to handle DST. This is what I came up with, a function that first subtracts 60 minutes from the datetime value if it falls within the date ranges given in the local table (LU_DST). Thus, the use will 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 am going to make a wild assumption that I am not the first person with this question. This is similar to what should have been added to one of the main link libraries. Is there a way to access my system clock to ask him if the DST was valid on specific dates and times?

 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

• I visited and imported the BAS file http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx . I spent at least an hour reading it, and although he can do his job well, I cannot figure out how to change it to my needs. But if you have an answer using your data structures, I will take a look.
• Time zones are not a problem, as it is all the time.

Edit

Thanks for the solutions, HansUp and Fenton, but I see an error in WbemScripting:

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

11/7/2009 6:15:00 PM

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

11/8/2009 7:15:00 PM

( GMT-05: 00). , , , DST 2009 1-, 7-. ?

Answer 1

. 2009 .

/ UTC 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

Immediate:

? 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

@HansUp, , , , :

  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

, , , True, .

, , , :

  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

UTC / ( ). , Call odateTime (True).

Answer 3

( )

, HansUp Fenton. , , Lotus Notes GMT .

, , wbemscripting API, , ( 2006 ).

'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

EDIT:. Lotus Notes, , .

Answer 4

UTC - DST, , .

, InteXX , : UTC Excel VBA

Microsoft Project.

: , ; GMT, UTC - https://www.timeanddate.com/time/gmt-utc-time.html