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
8
Medium Priority
?
1,918 Views
Last Modified: 2012-06-21
I need to convert this UTC timestamp to PST:

5/29/2008 2:06:00 PM
so it reads:
5/29/2008 6:06:00 AM

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

thanks.

0
Comment
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
  • Learn & ask questions
8 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 22747550
Use the DateAdd function like this:

DateAdd("h",4,Me.MyDateField)

this is for adding 4 hours to your field value.

jppinto
0
 
LVL 2

Expert Comment

by:Benji_
ID: 22747567
wouldnt it be -4 as you are taking away  ?
DateAdd("h",-4,Me.MyDateField)
0
 

Author Comment

by:Cole100
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:Cole100
ID: 22747613
UST - 8hrs = PST, I thought?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 22747711
Here is a generic solution.

Call it like this:

datUTC = #5/29/2008 2:06:00 PM#
lngBiasUTC = 0
lngBiasPST = 60 * 8
datPST = DateAddTimeZoneDiff(datUTC, lngBiasUTC, lngBiasPST)

/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.
  datRemote = DateAdd("n", lngBias, datLocal)
  
  DateAddTimeZoneDiff = datRemote
  
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

Open in new window

0
 

Author Comment

by:Cole100
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

by:Cole100
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

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

Select
  *,
  DateAddTimeZoneDiff([Clock_In_Datetime], 0, 480) As Clock_In_Datetime_PST,
  DateAddTimeZoneDiff([Clock_Out_Datetime], 0, 480) As Clock_Out_Datetime_PST
From
  tblYourTable

/gustav
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

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…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question