Adjusted Date/Time

[Jadiva]

Hi. If anyone can help with this, it is greatly appreciated.

I have a query that reads values from a linked table (via ODBC). One
particular field is an Eval_DateTime field that displays the date and time an
evaluation was performed in the format m/d/yyyy h:mm:ss AM/PM.

The problem that I'm having is that the date/time field in the table is off
by 5 hours because the server stores data in GMT time instead of EST. This
doesn't cause to much of a problem in the database until we have to run
monthly reports. Unfortunately, if an evaluation is done on 6/30/2008 7:25:36
PM, the server stores the time as 7/1/2008 12:25:36 AM. This throws our
numbers off every month because any evaluations that are done after 7:00 PM
on the last day of every month are being counted for the next month because
of the time difference on the server. Unfortunately, the system
administrators can not change the time on the server so I have to figure out
a way to do the time adjustment in the database.

I there a formula I can put into a module or query that will take the
Eval_DateTime values and return the EST (Eastern Standard Time)?

Thanks,

Jadiva

 

[Ken Sheridan]

Jadiva:

You can subtract 5 hours from the date/time value with the DateAdd function
so in a query for instance you might put:

SELECT SomeField, SomeOtherField,
DATEADD("h",-5, Eval_DateTime) AS Eval_DateTime_EST
FROM YourTable;

You can then base your report on the query.

Bear in mind that if the values in the base table are adjusted for British
Summer Time the start and end of this does not correspond with the start and
end of Daylight Saving Time, so in the interim you'd need to adjust the time
by plus or minus another hour.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

Jadiva

I will assume that you are using a query to 'collect' a month's data for
reporting.

You could use the DateAdd() function against the field to add 5 hours, then
base your report on that adjusted time.

No need to change the data at the source, just modify it as needed for
reporting purposes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jadiva]

Thanks a bunch! It worked like a charm and I learned something new.

Jadiva

 

[Graham Mandeno]

Hi Jadiva

You need to be a bit careful here. If you hard-code a 5-hour time
difference, what will happen when daylight saving starts or ends? What if
you want to use your application in another time zone?

The following code will convert a UTC (GMT) time to a local time, according
to the time zone setting for the computer it is running on:

============== start code =======================
Option Explicit

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

Private 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

Private Declare Function SystemTimeToTzSpecificLocalTime _
Lib "kernel32" ( _
lpTimeZoneInformation As TIME_ZONE_INFORMATION, _
lpUniversalTime As SYSTEMTIME, _
lpLocalTime As SYSTEMTIME _
) As Long

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

Public Function UTCtoLocal(ByVal tDate As Date) As Date
Dim tzi As TIME_ZONE_INFORMATION
Dim stUTC As SYSTEMTIME
Dim stLocal As SYSTEMTIME
Dim lRes As Long
lRes = GetTimeZoneInformation(tzi)
With stUTC
.wYear = YEAR(tDate)
.wMonth = Month(tDate)
.wDay = Day(tDate)
.wHour = Hour(tDate)
.wMinute = Minute(tDate)
.wSecond = Second(tDate)
.wMilliseconds = 0
End With
lRes = SystemTimeToTzSpecificLocalTime(tzi, stUTC, stLocal)
With stLocal
UTCtoLocal = DateSerial(.wYear, .wMonth, .wDay) _
+ TimeSerial(.wHour, .wMinute, .wSecond)
End With
End Function
============== end code =======================

Just copy and paste the code into a new module and then you can call it from
a calculated field in your query:

LocalTime: UTCtoLocal( [Eval_DateTime] )