Convert UTC time to local

ms-access, vba

Solution

I have found the following functions:

This one returns the UTC timezone offset:

Option Compare Database
Option Explicit

Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TimeZoneInfo) As Long


Private Type SystemTime
        intYear As Integer
        intMonth As Integer
        intwDayOfWeek As Integer
        intDay As Integer
        intHour As Integer
        intMinute As Integer
        intSecond As Integer
        intMilliseconds As Integer
End Type


Private Type TimeZoneInfo
        lngBias As Long
        intStandardName(32) As Integer
        intStandardDate As SystemTime
        intStandardBias As Long
        intDaylightName(32) As Integer
        intDaylightDate As SystemTime
        intDaylightBias As Long
End Type

Public Function GetUTCOffset() As Date
    Dim lngRet As Long
    Dim udtTZI As TimeZoneInfo

    lngRet = GetTimeZoneInformation(udtTZI)
    GetUTCOffset = udtTZI.lngBias / 60 / 24
End Function

source: [this site] (edit link no longer valid, removed)

And this one that converts a time to GMT:

Option Explicit
Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type
Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type

Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
'Purpose     :  Converts local time to GMT.
'Inputs      :  dtLocalDate                 The local data time to return as GMT.
'Outputs     :  Returns the local time in GMT.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :
'Revisions   :

Public Function ConvertLocalToGMT(dtLocalDate As Date) As Date
    Dim lSecsDiff As Long

    'Get the GMT time diff
    lSecsDiff = GetLocalToGMTDifference()
    'Return the time in GMT
    ConvertLocalToGMT = DateAdd("s", -lSecsDiff, dtLocalDate)
End Function

'Purpose     :  Converts GMT time to local time.
'Inputs      :  dtLocalDate                 The GMT data time to return as local time.
'Outputs     :  Returns GMT as local time.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :
'Revisions   :

Public Function ConvertGMTToLocal(gmtTime As Date) As Date
    Dim Differerence As Long

    Differerence = GetLocalToGMTDifference()
    ConvertGMTToLocal = DateAdd("s", Differerence, gmtTime)
End Function

'Purpose     :  Returns the time lDiff between local and GMT (secs).
'Inputs      :  dtLocalDate                 The local data time to return as GMT.
'Outputs     :  Returns the local time in GMT.
'Author      :  Andrew Baker
'Date        :  13/11/2002 10:16
'Notes       :  A positive number indicates your ahead of GMT.
'Revisions   :

Public Function GetLocalToGMTDifference() As Long
    Const TIME_ZONE_ID_INVALID& = &HFFFFFFFF
    Const TIME_ZONE_ID_STANDARD& = 1
    Const TIME_ZONE_ID_UNKNOWN& = 0
    Const TIME_ZONE_ID_DAYLIGHT& = 2

    Dim tTimeZoneInf As TIME_ZONE_INFORMATION
    Dim lRet As Long
    Dim lDiff As Long

    'Get time zone info
    lRet = GetTimeZoneInformation(tTimeZoneInf)

    'Convert diff to secs
    lDiff = -tTimeZoneInf.Bias * 60
    GetLocalToGMTDifference = lDiff

    'Check if we are in daylight saving time.
    If lRet = TIME_ZONE_ID_DAYLIGHT& Then
        'In daylight savings, apply the bias
        If tTimeZoneInf.DaylightDate.wMonth <> 0 Then
            'if tTimeZoneInf.DaylightDate.wMonth = 0 then the daylight
            'saving time change doesn't occur
            GetLocalToGMTDifference = lDiff - tTimeZoneInf.DaylightBias * 60
        End If
    End If
End Function

source : this site

The second one I believe uses the first one as well.

Either will suit your needs I believe.

Problem

I have an MS Access app which is used in two different time zones. The difference is 7 hours. I needed to find a time when the both of the offices are off so I can close their database and I can do compact and repair and do backup of them. So I will not need to create two separates front end and I tell close database one at 1000 PM and another in 4 AM I figure out I can say Close database at 00:30 AM UTC. But I do not know how to convert the same one in Local. Right now my code for closing the database looks like this: ``` Private Sub Form_Timer() Dim RunAtLocalTime As String RunAtLocalTime = Format(Now(), "HH:MM:SS") If RunAtLocalTime = ("00:00:00") Then DoCmd.Quit End If End Sub ``` And I want to do something like this: ``` Private Sub Form_Timer() Dim RunAtLocalTime As String Dim UTCTIME As '''RunAtLocalTime = Convert(UTCTIME) RunAtLocalTime = Format(Now(), "HH:MM:SS") If RunAtLocalTime = ("00:00:00") Then DoCmd.Quit End If End Sub ```

Original source