Solved

T-SQL equivalent to .NET method DateTIme.ToUniversalTime

Posted on 2008-10-30
5
1,009 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 125 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

16 Experts available now in Live!

Get 1:1 Help Now