hadyc
asked on
T-SQL equivalent to .NET method DateTIme.ToUniversalTime
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()
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()
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
C# it is then.
Thanks for your replies.
ASKER
Thanks for bouncing ideas back and forth.
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.