Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Converting UST columns to PST timezone

Posted on 2008-10-18
Medium Priority
1,918 Views
I need to convert this UTC timestamp to PST:

5/29/2008 2:06:00 PM
5/29/2008 6:06:00 AM

Assuming this requires an expression, Im not sure how to code this.

thanks.

0
Question by:Cole100
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 33

Expert Comment

ID: 22747550
Use the DateAdd function like this:

jppinto
0

LVL 2

Expert Comment

ID: 22747567
wouldnt it be -4 as you are taking away  ?
0

Author Comment

ID: 22747583
thanks

I need this to convert the whole columns data.....not just a single field.  My report is generated from a linked db and updates daily. I want to build an expression that 1. displays DATE()-1 and  2. converts the same UST columns to PST. Pardon my confusion.
0

Author Comment

ID: 22747613
UST - 8hrs = PST, I thought?
0

LVL 51

Expert Comment

ID: 22747711
Here is a generic solution.

Call it like this:

datUTC = #5/29/2008 2:06:00 PM#
lngBiasUTC = 0
lngBiasPST = 60 * 8

/gustav
``````Public Function DateAddTimeZoneDiff( _
ByVal datLocal As Date, _
ByVal lngLocalBias, _
ByVal lngRemoteBias) _
As Date

' Calculates the date/time of datLocal in a remote time zone.
' Adds the difference in minutes between the local time zone bias and
' the remote time zone bias, if both bias are relative to UTC.
'
' Examples:
'
'   datRemote = DateAddTimeZoneDiff(Now(), 60, -600)
' will return datRemote as eleven hours ahead of local time.
'
'   datRemote = DateAddTimeZoneDiff(Now(), -600, 60)
' will return datRemote as eleven hours behind local time.
'
' 2000-05-30. Cactus Data ApS, CPH.
' 2005-01-25. Adjusted to accommodate change in TimeZoneBiasDiff().

Dim datRemote As Date
Dim lngBias   As Long

' Get difference (in minutes) in time zone bias.
lngBias = TimeZoneBiasDiff(lngLocalBias, lngRemoteBias)
' Calculate remote date/time.

End Function

Public Function TimeZoneBiasDiff( _
ByVal lngLocalTimeBias As Long, _
ByVal lngRemoteTimeBias As Long) _
As Long

' Calculates the difference in minutes between two time zones,
' typically from the local time zone to the remote time zone.
' Both time zones must be expressed by their bias relative to
' UTC (Coordinated Universal Time) which is measured in minutes.
'
' 2000-05-30. Cactus Data ApS, CPH.
' 2005-01-25. Output reversed to express bias value.

' Minimum amount of minutes for a time zone bias.
Const clngTimeZoneBiasMin As Long = 15

Dim lngTimeZoneBiasDiff As Long

' Round off time zone bias by minimum time zone difference.
lngLocalTimeBias = lngLocalTimeBias \ clngTimeZoneBiasMin
lngLocalTimeBias = lngLocalTimeBias * clngTimeZoneBiasMin
lngRemoteTimeBias = lngRemoteTimeBias \ clngTimeZoneBiasMin
lngRemoteTimeBias = lngRemoteTimeBias * clngTimeZoneBiasMin

' Calculate difference in time zone bias.
lngTimeZoneBiasDiff = lngLocalTimeBias - lngRemoteTimeBias

TimeZoneBiasDiff = lngTimeZoneBiasDiff

End Function
``````
0

Author Comment

ID: 22749507
I dont want to put in a specific datetime..........I need all the fields under the timestamp column to be converted to PST.

0

Author Comment

ID: 22749566
specifically:  I need all fields to convert for cloockin datetime and clockout datetime.

Clock_In_Datetime      Clock_Out_Datetime      EOD Clockout Flag

Last name      First name      10/17/2008 5:00:00 PM      10/17/2008 9:43:00 PM                  0
Last name      First name      10/17/2008 10:25:00 PM      10/18/2008 1:11:00 AM                 -1

0

LVL 51

Accepted Solution

Gustav Brock earned 2000 total points
ID: 22750049
Well, then create a query where you use the function:

Select
*,
From
tblYourTable

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
###### Suggested Courses
Course of the Month6 days, 1 hour left to enroll