[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Convert SQL datetime from UTC to local

Posted on 2009-04-23
9
Medium Priority
?
3,103 Views
Last Modified: 2012-05-06
I have a MS SQL database in which all datetimes are stored as UTC(GMT).  All work that this database keeps track of is in the EST time zone.  I need to make a stored procedure that can convert a given datetime to whatever the local time should be.  I've figured out how to do this with an offset and the DATEADD() function, but I dont know how to determine the appropriate offset for the datetime.  I could use this:

DATEADD(hour,DATEDIFF(hour,GETUTCDATE(),GETDATE()),@varDate)

but this calculates the offset based on the current time, which fails in the case where I am calculating a datetime from daylight savings at a time that isnt in daylight savings.

It seems like if I could seed GETDATE and GETUTCDATE with the date I am working with that would work, but I dont know how or if I can do that.

Thanks!
0
Comment
Question by:jdroger2
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 9

Expert Comment

by:Seven price
ID: 24216997
there is a defined function in the CRM sql database called "fn_UTCToLocalTime"
0
 
LVL 1

Author Comment

by:jdroger2
ID: 24217064
What do you mean CRM sql database?
0
 
LVL 9

Expert Comment

by:Seven price
ID: 24217111
Sorry try this. You are not using the crm

UTC datetime values in SQL Server 2000
You can convert local datetime values to UTC datetime values, and vice-versa, using the built-in GETUTCDATE() function:
DECLARE @LocalDate DATETIME
SET @LocalDate = GETDATE()
-- convert local date to utc date
DECLARE @UTCDate DATETIME
SET @UTCDate = DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @LocalDate)
-- convert utc date to local date
DECLARE @LocalDate2 DATETIME
SET @LocalDate2 = DATEADD(Hour, DATEDIFF(Hour, GETDATE(), GETUTCDATE()), @UTCDate)
SELECT @LocalDate, @UTCDate, @LocalDate2
Note that GETUTCDATE() returns the current datetime in UTC.  By comparing the value with GETDATE() we can determine the time zone, which can then be used to adjust any date.  
I tried to bake these expressions into a set of user-defined functions, but SQL Server complained because user-defined functions cannot call non-deterministic functions (in this case GETDATE()/GETUTCDATE()).
 
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:jdroger2
ID: 24217162
these fns, getdate and getutcdate return the CURRENT datetimes don't they? As I said in the question, this fails for the situation where I am GMT-5 some parts of the year and GMT-4 other times.
0
 
LVL 19

Expert Comment

by:folderol
ID: 24217904
There are several web discussions about this issue,  the most versatile solution offered is to create a timezone / DST table which you can lookup the appropriate offset hours from UTC.  I saw some mention of obtaining the server's DST status via API but no example code.
Using the current date and the results of datediff(hour, getdate(), getutcdate()) as a key you could return from the table the timezone and whether daylight savings was in effect for that date.
0
 
LVL 1

Author Comment

by:jdroger2
ID: 24218050
so there is no way to ask the operating system via SQL Server?  I know there are libraries in php for example where you can pass a date and it can return to you whether or not the given date is in DST and I think it queries the OS because it works even when DST changes, as long as the OS is up to date patch wise.  folderol, what did you google to find these threads that discuss alternatives?
0
 
LVL 19

Expert Comment

by:folderol
ID: 24218729
google search
"function to return the time zone from Windows"

Discusses the change in USA for start of DST
this is the API calls.  you have to use some method like this as SQL Server does not have an internal function or statement that returns the current server setting.
http://msdn.microsoft.com/en-us/library/ms724421(VS.85).aspx

Discusses using registry from within EXCEL,
http://www.pcreview.co.uk/forums/thread-2788903.php

0
 
LVL 19

Accepted Solution

by:
folderol earned 2000 total points
ID: 24218809
Link to a solution that has a downloadable database of timezones, in standard TIME_ZONE_INFORMATION structure.

http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx
0
 

Expert Comment

by:fskilnik
ID: 34492162
Uau, I needed exactly the same thing and the accepted solution is MARVELLOUS!

Thanks a LOT, guys!

Regards,
fskilnik.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

830 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