?
Solved

T-SQL equivalent to .NET method DateTIme.ToUniversalTime

Posted on 2008-10-30
5
Medium Priority
?
1,157 Views
Last Modified: 2010-04-21
I am looking for a T-SQL equivalent to the .NET method DateTIme.ToUniversalTime().

I have a database storing Local datetime data, and I am trying to write a script that converts them all to UTC. If I use
SET @offset = DATEDIFF(minute, GETDATE(), GETUTCDATE())
followed by
SET @utcdate = DATEADD(minute, @offset, @columnvalue)
the offset being constant won't cut it because this doesn't take into account daylight savings time (never mind syntax errors).

Here is an example of what I am trying to achieve (I live in the EST timezone): Oct 10, 2008 12:00 AM (in daylight savings) should be converted to Oct 10, 2008 04:00 AM, whereas Dec 10, 2008 12:00 AM (out of daylight savings) should become Dec 10, 2008 05:00 AM. Notice 04:00 AM vs 05:00 AM.

DateTIme.ToUniversalTime() takes care of daylight savings implicitly. I there a way to achieve the same in T-SQL?
0
Comment
Question by:hadyc
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22845199
SET @offset = DATEDIFF(minute, GETDATE(), GETUTCDATE())
followed by
SET @utcdate = DATEADD(minute, @offset, @columnvalue)


The problem that you have is that your DATA is stored in LOCAL time.  And put shortly, SQL doesn't know that.  So you you insert GETDATE() into a column vs. GETUTCDATE(), SQL Server doesn't know or care.  It just knows that you put a date/time value in there.  That is why you should always store date/time values in UTC.
0
 

Author Comment

by:hadyc
ID: 22850107
Brandon,

I understand what you are saying. That is precisely why we are trying to upgrade the database.
We are assuming that all datetime values currently in there are Local time, and want to convert them to UTC.

In C#, you can set a DateTime object 's Kind to be Local, then you can invoke its ToUniversalTime() method to convert to UTC; that will implicitly offset the datetime by 4 or 5 hours (database located in EST) depending on whether the date falls in or out of daylight savings time, respectively.

As I described in the previous post, our first attempt in SQL was to mimic the C# conversion method by getting the (Local - UTC) offset, but that's not good enough.

I may just have to write the conversion logic in C# instead of pure SQL script.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22850390
Your question is " I there a way to achieve the same in T-SQL?"
Well the answer is "YES".  

SQL Server does not have a function that will convert a date, presumed to be in LOCAL time to the server, into UTC.  You would have to write it.  So it can be done, but you have to find all the rules and do it yourself.

It may be simpler though to handle this in C# if you already know how.  

Sorry that the answer isn't what you were looking for, but it's the answer.
0
 

Author Comment

by:hadyc
ID: 22850953
I see your point. I certainly want to avoid writing rules in SQL for daylight savings. I take it there is no SQL built-in function that implicitly handles that.

C# it is then.

Thanks for your replies.
0
 

Author Closing Comment

by:hadyc
ID: 31511849
Thanks for bouncing ideas back and forth.
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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

599 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