Solved

T-SQL equivalent to .NET method DateTIme.ToUniversalTime

Posted on 2008-10-30
5
1,078 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

695 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