September 10, 2009 at 1:00 pm
I have a datetime column where the data is stored in UTC time zone.
I need to create a view showing the data in the local time zone.
Is there a one line command to do this?
September 10, 2009 at 1:19 pm
Just off the top of my head you could look at doing something like....
DECLARE @datetoChange AS DATETIME
SELECT @datetoChange = '2009-01-01 12:00:00.000'
SELECT @datetoChange,
DATEDIFF(hh,GETUTCDATE(), GETDATE()) AS DIFFfromUTC,
DATEADD(hh,DATEDIFF(hh,GETUTCDATE(), GETDATE()),@datetoChange) AS modifiedDate
There are some limitations of this however... It does not take into account that a date may or maynot be in Daylight Savings Time. In other words if there is a date in July (and that happens to be in DST for your location) but you're querying the view in December (and that's not in DST for you) it will add or subtract the extra hour...
Alternatively, you could perhaps build a function that returns the appropriate offset from UTC based on the times your area changes from DST. This is of course moot if this is for an app that would span multiple timezones/countries where the DST rules might be different.
This is something I'd probably be more apt to do in the User interface, rather than TSQL, as it seems more for display rather than datastorage. Also you might be able to tie into the windows time system to return the appropriate UTC offsets for the appropriate dates?
-Hopefully this at least gets you moving int he right direction...
-Luke.
Edit added comments about a utc offset function and bit about tieing into the windows system.
September 10, 2009 at 1:34 pm
So this looks like a 3 line command, I really want something like this
select convert(utcdatecolumn),column2, column3
from table
without creating a function, there is no way to do it?
September 10, 2009 at 1:42 pm
like I said the solution has some limitations but it does meet the criterion for a inline command. 3 lines were put in to provide some sample data (which you did not) and to help you understand what was happening... Perhaps you should look at the select statement again and see what it does? Notice it outputs 3 columns, separated by linebreaks for readability...
The idea was for you to take the next step and clarify exactly what you are trying to do. Will DST be a problem for you. Are you using this in various Timezones? It was to help you more thoroughly describe your problem, not answer the exact question for you. Perhaps with a better description of what you wish to accomplish including perhaps some why's you may find a better result.
-Luke.
September 10, 2009 at 1:52 pm
Sorry,
Your solution is PERFECT
datediff(hh,utcdate,getdate()) is exactly what I need!
September 10, 2009 at 2:03 pm
October 14, 2010 at 7:49 am
Perhaps a slight improvement is to calculate the offset in minutes as some time zones are offset by 1/2 hour, for example Newfoundland time.
DECLARE @datetoChange AS DATETIME
SELECT @datetoChange = '2009-01-01 12:00:00.000'
SELECT @datetoChange,
DATEDIFF(minute, GETUTCDATE(), GETDATE()) AS DIFFfromUTC,
DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), GETDATE()),@datetoChange) AS modifiedDate
The Time Zone limitations mentioned in the original post remain of course.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply