Converting UST columns to PST timezone

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.

Cole100IT Systems ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jppintoCommented:
Use the DateAdd function like this:

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

this is for adding 4 hours to your field value.

jppinto
0
Benji_Commented:
wouldnt it be -4 as you are taking away  ?
DateAdd("h",-4,Me.MyDateField)
0
Cole100IT Systems ManagerAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Cole100IT Systems ManagerAuthor Commented:
UST - 8hrs = PST, I thought?
0
Gustav BrockCIOCommented:
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
Cole100IT Systems ManagerAuthor Commented:
I dont want to put in a specific datetime..........I need all the fields under the timestamp column to be converted to PST.

0
Cole100IT Systems ManagerAuthor Commented:
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
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Regular Expressions

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.