Solved

Converting UST columns to PST timezone

Posted on 2008-10-18
8
1,904 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
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
 

Author Comment

by:Cole100
ID: 22747613
UST - 8hrs = PST, I thought?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 49

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 49

Accepted Solution

by:
Gustav Brock earned 500 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now