• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1944
  • Last Modified:

SQL table date/time stored in UTC, need Access function to convert to local date / time

I have a database that has all of the dates / time fields stored in UTC time.  I need a function in access to display the date / time based on the local time zone.

Example: in sql i have a date / time stored for a change to a record and it is 5 hours ahead of my local time.  

in sql  9/4/2012 5:15:27 PM  I'd like to view this in
access in central time (GMT -6) - 9/4/2012 12:15:27PM

Thank you in advance.
0
BFanguy
Asked:
BFanguy
3 Solutions
 
BFanguyAuthor Commented:
is this all i need to do?

DateAdd("h",-5,[Date])

does this take care of daylight savings time.....
0
 
hnasrCommented:
This just subtracts 5 hours from date.

Daylight savings has to be managed in the original data captured.

You can check by listing few values including Daylight savings entries.
0
 
Gustav BrockCIOCommented:
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
lwadwellCommented:
I tried the functions from here http://www.vbusers.com/code/codeget.asp?PostID=1&ThreadID=632

They seem to work fine in doing a local->UTC (GMT) and back based on limited testing by just doing:
select now() as current_time, ConvertLocalToGMT(now()), ConvertGMTToLocal(now()), datediff("h", ConvertLocalToGMT(now()), now()),  datediff("h", ConvertGMTToLocal(now()), now())
0
 
BFanguyAuthor Commented:
Thanks guys,  I will try all 3 solutions.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now