UTC format to Local Time

[kcyankees125]

Fairly new to Access... and need help.

I have a data source that gives me time in the UTC format with time zone adjustment given: "0000+0000". This is in the field of a table called [DEPT_TIME]. How do I convert this to local time? For example "0230-0600" should give me something along the lines of (but not necessarily exactly) "8:30 PM"

Thanks in advance!

 

[DrallocD]

Copy the code below into a module and you can call UTCToLocal() to convert from a UTC datetime to the local equivalent.

UTCToLocal()

Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
 
Public Enum TIME_ZONE
    TIME_ZONE_ID_INVALID = 0
    TIME_ZONE_STANDARD = 1
    TIME_ZONE_DAYLIGHT = 2
End Enum
 
Public 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
 
Public Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName(0 To 31) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName(0 To 31) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type
 
Public Function LocalOffsetFromUTC() As Double
    Dim TZI As TIME_ZONE_INFORMATION
    Dim DST As TIME_ZONE
    DST = GetTimeZoneInformation(TZI)
    If DST = TIME_ZONE_DAYLIGHT Then
        LocalOffsetFromUTC = (TZI.Bias + TZI.DaylightBias) / 60
    Else
        LocalOffsetFromUTC = TZI.Bias / 60
    End If
End Function
 
Public Function UTCToLocal(ByRef dUTC As Date)
    UTCToLocal = DateAdd("h", LocalOffsetFromUTC, dUTC)
End Function

 

[kcyankees125]

Great Thanks! Since I'm fairly new to using modules and VBA and what not... How do I call UTCToLocal() in a query on a specific field named [DEPT_TIME]? I'd like to name the new field: DEPT_TIME_LOCAL.

 

[DrallocD]

Assuming DEPT_TIME is a date, you would use:

SELECT DEPT_TIME,  UTCToLocal(DEPT_TIME) AS DEPT_TIME_LOCAL FROM xxxxx