Solved

Convert PST time to GMT or IST in MS ACCESS

Posted on 2007-12-06
3
43,938 Views
Last Modified: 2010-04-21
I've a issue where the columns stores the date, starttime and end time in PST time zone. I want to add another column (computed if possible) and convert the date/time to IST. Is this possible? How can I do it?
0
Comment
Question by:rsrsm
3 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 125 total points
ID: 20419175
Obtain the time zone bias of the two time zones and apply the functions below.

/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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 20425260
rsrsm,

According to this link:
http://www.timegenie.com/timezones.php#notes_about_GMT
IST can mean Indian Standard Time OR Ireland Standard time, so be carefull in your terminlogy.
;)

If you have PST (Pacific Standard Time) you must add 13.5 hours (810 minutes) to get Indian Standard Time.

If you want to use a Query, it would look something like this:

SELECT tblTimes.PST, DateAdd("n",810,[PST]) AS IST
FROM tblTimes;

Here is a sample:
https://filedb.experts-exchange.com/incoming/ee-stuff/6036-Access--EE-Q-23005637Convert-Ti.zip

JeffCoachman

0
 

Author Closing Comment

by:rsrsm
ID: 31413108
I was able to arrive at a solution using both the answers, hence I'm accepting both the answers and splitting the points.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

911 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

20 Experts available now in Live!

Get 1:1 Help Now