How convert GMT to LocalTime in Access 2003


G

Guest

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
 
Ad

Advertisements

A

Al Campagna

Anita,
That would be a calculated field, that utilizes the DateAdd function.
=DateAdd("h",-4,[YourGMTTimeField])

In this example, my time is Eastern Standard, which is 4 hours less than
GMT.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
 
G

Guest

Thanks,
But my timezone is not fixed. I want generalized solution.

Al Campagna said:
Anita,
That would be a calculated field, that utilizes the DateAdd function.
=DateAdd("h",-4,[YourGMTTimeField])

In this example, my time is Eastern Standard, which is 4 hours less than
GMT.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

Anita Tadhani said:
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
 
G

Guest

Shall I need to write module to get it work.
Well I have no experience with Access.
Thanks
 
M

Michel Walsh

Yes. You have to place the function calling the Win32 API method in a
standard module and the function should not be private.


UNFORTUNATELY, the example supplied do it under a FORM, don't ! It has to
be in a standard module, not a from, not a report, not a class.


Hoping it may help,
Vanderghast, Access MVP
 
Ad

Advertisements

G

Gary Walter

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
 
G

Guest

Thanks Everyone.
I got it work for Daylight saving too.

My updated function

Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32"
(lpTimeZoneInformation As TimeZoneInfo, lpUniversalTime As SystemTime,
lpLocalTime As SystemTime) As Long

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

'Get TimeZone information
lngRet = GetTimeZoneInformation(udtTZI)

stUTC.intYear = Year(pUTC)
stUTC.intMonth = Month(pUTC)
stUTC.intDay = Day(pUTC)
stUTC.intHour = Hour(pUTC)
stUTC.intMinute = Minute(pUTC)
stUTC.intSecond = Second(pUTC)
stUTC.intMilliseconds = 0

'Converts a time in Coordinated Universal Time (UTC) to a specified time
zone's corresponding local time.
'It takes into account whether the time to be converted falls in
daylight saving time or not

lngRet = SystemTimeToTzSpecificLocalTime(udtTZI, stUTC, stLocal)

'Return Date type
fConvertUTCtoLocalTime = DateSerial(stLocal.intYear, stLocal.intMonth,
stLocal.intDay) + TimeSerial(stLocal.intHour, stLocal.intMinute,
stLocal.intSecond)

Exit_fConvertUTCtoLocalTime:
Exit Function

Err_fConvertUTCtoLocalTime:
MsgBox Err.Description
Resume Exit_fConvertUTCtoLocalTime
End Function

Gary Walter said:
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 said:
Shall I need to write module to get it work.
Well I have no experience with Access.
Thanks
 
Ad

Advertisements

Joined
Jun 30, 2015
Messages
1
Reaction score
0
Thanks, this update works perfectly as long as you use the function supplied by Gary:
LocTime: fConvertUTCtoLocalTime([UTC])
in your query, replacing UTC with your field in the square brackets. It needs to run for each record to get the daylight savings time accurate

Thanks Everyone.
I got it work for Daylight saving too.

My updated function

Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32"
(lpTimeZoneInformation As TimeZoneInfo, lpUniversalTime As SystemTime,
lpLocalTime As SystemTime) As Long

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

'Get TimeZone information
lngRet = GetTimeZoneInformation(udtTZI)

stUTC.intYear = Year(pUTC)
stUTC.intMonth = Month(pUTC)
stUTC.intDay = Day(pUTC)
stUTC.intHour = Hour(pUTC)
stUTC.intMinute = Minute(pUTC)
stUTC.intSecond = Second(pUTC)
stUTC.intMilliseconds = 0

'Converts a time in Coordinated Universal Time (UTC) to a specified time
zone's corresponding local time.
'It takes into account whether the time to be converted falls in
daylight saving time or not

lngRet = SystemTimeToTzSpecificLocalTime(udtTZI, stUTC, stLocal)

'Return Date type
fConvertUTCtoLocalTime = DateSerial(stLocal.intYear, stLocal.intMonth,
stLocal.intDay) + TimeSerial(stLocal.intHour, stLocal.intMinute,
stLocal.intSecond)

Exit_fConvertUTCtoLocalTime:
Exit Function

Err_fConvertUTCtoLocalTime:
MsgBox Err.Description
Resume Exit_fConvertUTCtoLocalTime
End Function

"Gary Walter" wrote:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top