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