Re: How convert GMT to LocalTime in Access 2003

Hi Anita,

I'm not sure if you have opened a can
of worms (with all daylight savings silliness),
but try this:

Start a new module.
(all you should see is Option Compare Database)

Copy the following into new module:

'*** start code ***
Option Explicit

'following code adapted from
'http://www.access-programmers.co.uk/
' forums/showthread.php?s=&threadid=55810

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 fGetUTCLocalBiasMinutes() As Long
On Error GoTo Err_fGetUTCLocalBiasMinutes
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo

'UTC = local time + bias
'The bias is the difference, in minutes,
'between UTC time and local time.
'==> local time = UTC - bias minutes


lngRet = GetTimeZoneInformation(udtTZI)
fGetUTCLocalBiasMinutes = udtTZI.lngBias

Exit_fGetUTCLocalBiasMinutes:
Exit Function

Err_fGetUTCLocalBiasMinutes:
MsgBox Err.Description
Resume Exit_fGetUTCLocalBiasMinutes
End Function

Public Function fGetUTCLocalBiasHours() As Long
On Error GoTo Err_fGetUTCLocalBiasHours
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo

'UTC = local time + bias
'The bias is the difference, in minutes,
'between UTC time and local time.
'==> local time = UTC - (bias / 60) hours

lngRet = GetTimeZoneInformation(udtTZI)
fGetUTCLocalBiasHours = udtTZI.lngBias / 60

Exit_fGetUTCLocalBiasHours:
Exit Function

Err_fGetUTCLocalBiasHours:
MsgBox Err.Description
Resume Exit_fGetUTCLocalBiasHours
End Function

Public Function fConvertUTCtoLocalTime(pUTC As Date) As Date
On Error GoTo Err_fConvertUTCtoLocalTime
Dim lngRet As Long
Dim udtTZI As TimeZoneInfo
Dim lngBiasMinutes As Long

'UTC = local time + bias
'The bias is the difference, in minutes,
'between UTC time and local time.
'==> local time = UTC - bias
'==> local time = DateAdd("n",-bias, UTC)

lngRet = GetTimeZoneInformation(udtTZI)
lngBiasMinutes = udtTZI.lngBias

fConvertUTCtoLocalTime = DateAdd("n", -lngBiasMinutes, pUTC)

Exit_fConvertUTCtoLocalTime:
Exit Function

Err_fConvertUTCtoLocalTime:
MsgBox Err.Description
Resume Exit_fConvertUTCtoLocalTime
End Function
'*** end code ***

Save your module (say as "modUTC")

Double-check that "word-wrap" has not
orphaned some lines that should be all on
one line.

Click on Debug/Compile in top menu
to verify everything okay.

In a query, try this for one field row in the grid:
(replace "[UTC]" with actual field name)

LocTime: DateAdd("h",-fGetUTCLocalBiasHours(), [UTC])

I think (I could be wrong) that since function
returns a scalar that it will be run only once
when you run the query

versus if you tried

LocTime: fConvertUTCtoLocalTime([UTC])

which I know would be run for every record!

Or...

in a query that *for sure* will run function only once,
add field row to grid that provides this bias

Bias: - fGetUTCLocalBiasHours()

then, in another field row of query grid, do your conversion
(again replacing "[UTC]" with actual field name)

LocTime: DateAdd("h", [Bias], [UTC])

As mentioned earlier, I don't know how daylight
savings time silliness will effect your results in
every situation.

good luck,

gary



"Anita Tadhani" wrote:
Shall I need to write module to get it work.
Well I have no experience with Access.
Thanks

"raskew via AccessMonster.com" wrote:

Hi -

See:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=55810

HTH - Bob

Anita Tadhani wrote:
I have Access 2003 database stores Time in GMT. I want to convert this
GMT
time into localtime when I display in Report.
How should I write the query?
Thanks

--
Message posted via http://www.accessmonster.com




.

Relevant Pages

  • Re: [SLE] NTP Server - US Daylight Savings Time
    ... the system gives you the local time, calculated from the UTC time + ... depending on their local adjustments: ... Dos/windows expect the CMOS clock to be at local time instead... ...
    (SuSE)
  • Re: Reading Time zone offset
    ... ' Obtain local Time Zone bias from machine registry. ... lngBias = lngBiasKey ... script I get -60 for lngBias. ...
    (microsoft.public.scripting.vbscript)
  • How to translate from UTC/GMT time to local time?
    ... The problem with translating those times to a local time is that one does not know for each UTC time whether the local time at that same moment had daylight savings time in effect. ... If one subtracts DiffFactorHours from a UTC time one will get one's local time. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: [opensuse] Panel problems
    ... The date command, by default, shows local time. ... I think it can also be used to set utc time. ... set it in the bios myself first. ...
    (SuSE)
  • Re: Best practice for TOD clock
    ... One GREAT reason for using UTC time with local time ... > local applications that log with local time) for one hour in the fall. ... > duplicate timestamps if the timestamps are using UTC time. ... If "the meridian" you mention is the Greenwich meridian, ...
    (bit.listserv.ibm-main)