Solved

Converting UST columns to PST timezone

Posted on 2008-10-18
8
1,907 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Whatever be the reason, if you are working on web development side,  you will need day-today validation codes like email validation, date validation , IP address validation, phone validation on any of the edit page or say at the time of registration…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

948 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

21 Experts available now in Live!

Get 1:1 Help Now