Code to convert GMT time to Current time zone time, including DST adjustment? (1 Viewer)

[sportsguy]

I know that it exists in our system somewhere, but its not in VBA.

Anybody have that code anywhere? or know where i can get it?

thanks

sportsguy

 

[RuralGuy]

Maybe this link will help.

 

[sportsguy]

I am not quite that good. . .

Access hack. . . though I can key a table correctly. .

If I can figure out whether a random date is within
daylight savings time or not, then I can take GMT, which is
provided, and back into the correct local time. . .

I just can't figure out how this evaluates daylight saving time
or not. . .

sorry, this is beyond me at the moment. . .

thanks

 

[sportsguy]

Nice coding challenge.

What I would like is to take Zulu date/time, GMT, UCT date/time,
and convert it to local date/time. then examine local time and adjust for daylight savings time. . .

that way, I can get my fiscal close date and time correct.

unfortunately, I don't know how to interpret the TZI information retrieved through the api

thanks
sportsguy

 

[RuralGuy]

I added some Debug.Print statements to the code so you can see what is returned in the immediate window. Put this *all* in a separate module and then play with it in the immediate window.

Option Compare Database
Option Explicit

'************************** Code Start ***********************
'This code was originally written by Terry Kreft & Michel Walsh
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft & Michel Walsh
'
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

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

Declare Function GetTimeZoneInformation Lib "kernel32" _
  (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long

Function PreciseDateDiff(Interval As String, ByVal Date1, ByVal Date2, _
                        Optional FirstDayOfWeek As Integer = vbSunday, _
                        Optional FirstWeekOfYear As Integer = vbFirstJan1) _
                        As Long
'From an original idea by Michel Walsh
'Get a DateDiff, taking into account the time light saving
'
'Usage Example:
'
'   ? PreciseDateDiff("h", #1/1/90#, #5/5/98#)
'
  Dim lngRet As Long
  Dim x As Integer
  Dim TZI As TIME_ZONE_INFORMATION
  Dim strEval As String
  If Eval("'" & Interval & "' in ('h','n','s')") Then
    If FirstDayOfWeek >= 0 And FirstDayOfWeek <= 7 Then
      If FirstWeekOfYear >= 0 And FirstWeekOfYear <= 3 Then
        lngRet = GetTimeZoneInformation(TZI)
        strEval = DateForSQL(Date1) & " between " _
                & DateForSQL(SummerTime(Year(Date1))) & " and " _
                & DateForSQL(StandardTime(Year(Date1)))
        If Eval(strEval) Then
          Date1 = DateAdd("n", TZI.DaylightBias, Date1)
        End If
        strEval = DateForSQL(Date2) & " between " _
                & DateForSQL(SummerTime(Year(Date2))) & " and " _
                & DateForSQL(StandardTime(Year(Date2)))

[COLOR="Red"]'-- Add this code in and play with it in the immediate window.
'--
'-- I just put:
'-- ? PreciseDateDiff("h", #1/1/90#, #5/5/98#)
'-- in the immediate window to see what was going on.
'--
'-- I still haven't figured out everything but thought you could play with it.

Debug.Print "Current Bias " & TZI.Bias & " minutes or " & TZI.Bias / 60 & " hours."
For x = 0 To 30
  Debug.Print Chr(TZI.DaylightName(x));
Next
Debug.Print
Debug.Print "DaylightBias " & TZI.DaylightBias & " minutes or "; TZI.DaylightBias / 60 & " hour(s)."
Debug.Print "            Daylight Savings starts"
Debug.Print "DaylightDate.wDayOfWeek [" & TZI.DaylightDate.wDayOfWeek & "]"
Debug.Print "DaylightDate.wMonth [" & TZI.DaylightDate.wMonth & "]"
Debug.Print "DaylightDate.wDay [" & TZI.DaylightDate.wDay & "]"
Debug.Print "DaylightDate.wHour [" & TZI.DaylightDate.wHour & "]"
Debug.Print "DaylightDate.wMinute [" & TZI.DaylightDate.wMinute & "]"
Debug.Print "DaylightDate.wSecond [" & TZI.DaylightDate.wSecond & "]"
Debug.Print "DaylightDate.wMilliseconds [" & TZI.DaylightDate.wMilliseconds & "]"

For x = 0 To 30
  Debug.Print Chr(TZI.StandardName(x));
Next
Debug.Print
Debug.Print "StandardBias " & TZI.StandardBias & " minutes or "; TZI.StandardBias / 60 & " hour(s)."
Debug.Print "            Daylight Savings ends"
Debug.Print "StandardDate.wDayOfWeek [" & TZI.StandardDate.wDayOfWeek & "]"
Debug.Print "StandardDate.wMonth [" & TZI.StandardDate.wMonth & "]"
Debug.Print "StandardDate.wDay [" & TZI.StandardDate.wDay & "]"
Debug.Print "StandardDate.wHour [" & TZI.StandardDate.wHour & "]"
Debug.Print "StandardDate.wMinute [" & TZI.StandardDate.wMinute & "]"
Debug.Print "StandardDate.wSecond [" & TZI.StandardDate.wSecond & "]"
Debug.Print "StandardDate.wMilliseconds [" & TZI.StandardDate.wMilliseconds & "]"[/COLOR]

        If Eval(strEval) Then
          Date2 = DateAdd("n", TZI.DaylightBias, Date2)
        End If
        lngRet = DateDiff(Interval, Date1, Date2, _
                                    FirstDayOfWeek, FirstWeekOfYear)
        PreciseDateDiff = lngRet
      End If
    End If
  Else
    PreciseDateDiff = DateDiff(Interval, Date1, Date2, FirstDayOfWeek, FirstWeekOfYear)
  End If
End Function

Private Function DateForSQL(dteDate) As String
  DateForSQL = Format(dteDate, "\#m/dd/yyyy h:nn:ss AM/PM \#")
End Function


Public Function SummerTime(Optional intYear As Long = -1) As Date
    ' Originally submitted by Terry Kreft
    '   modified to accept an optional year

    If -1 = intYear Then intYear = Year(Date)
    ' Get this year, by defaut, not -1
    
    Dim lngRet As Long
    Dim TZI As TIME_ZONE_INFORMATION
    lngRet = GetTimeZoneInformation(TZI)
    With TZI.DaylightDate
        SummerTime = CVDate(GetSundate(.wMonth, .wDay, _
                                    intYear) + (.wHour / 24))
    End With
End Function

Public Function StandardTime(Optional intYear As Long = -1) As Date
    ' Originally submitted by Terry Kreft
    '   modified to accept an optinal year

    If -1 = intYear Then intYear = Year(Date)
    ' Get this year, by defaut, not -1
    
    Dim lngRet As Long
    Dim TZI As TIME_ZONE_INFORMATION
    lngRet = GetTimeZoneInformation(TZI)
    With TZI.StandardDate
        StandardTime = CVDate(GetSundate(.wMonth, .wDay, _
                                    intYear) + (.wHour / 24))
    End With
End Function

Private Function GetSundate(intMonth As Integer, _
                            intSun As Integer, _
                            Optional intYear As Long = -1) _
                            As Date
' Originally submitted by Terry Kreft
'   Modified to set any Year

    If intYear = -1 Then intYear = Year(Date)
    ' if not supplied, get this Year
    
    Dim varRet As Variant
    Dim intDayOfWeek As Integer
    
    varRet = DateSerial(intYear, intMonth, 1)
    ' avoid regional setting problem
    
    intDayOfWeek = Weekday(varRet)
    If intDayOfWeek <> 1 Then
        varRet = DateAdd("d", 8 - intDayOfWeek, varRet)
    End If
    varRet = DateAdd("ww", intSun - 1, varRet)
    GetSundate = varRet
End Function
'************************** Code End ***********************

 

[sportsguy]

thanks

that's exactly what i spent 3 hours last night doing with that code.

turns out that we use a standard offset of EST or -5h
so I just have to use dateadd function, and I am all set.

However, it still would be a cool piece of code to write

thanks

sportsguy

 

[AlienRay]

I've been using this code for a database that is currently being used in New York, London and India. The New York and India users are having no problems, but the London users are now reporting that it still thinks they're on DST. It appears the StandardDate() function is returning November 4, 2007, which is the date DST will end in the U.S., but not the U.K. Does anyone know why this would be?

 

[raskew]

Hi -

See if this is of any help:

http://www.uic.edu/depts/accc/systems/daylight/windows.html

Bob