Solved

T-SQL equivalent to .NET method DateTIme.ToUniversalTime

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

813 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

13 Experts available now in Live!

Get 1:1 Help Now