locked
How do I convert UTC to specified time zone in T-SQL (SQL Server 2008 R2)? RRS feed

  • Question

  • Question: How do I convert UTC time to specified time zone using SQL in SQL Server 2008 R2?

    ---------------------------------------------------

    Scenario: I have a database/ASP.NET application that I use to manage people from quite a few different time zones. In order to keep activities coordinated, all the times in the database are UTC time. Each user's TimeZone is also registered in the database using .NET Framework time zoneIDs e.g. Eastern Standard Time.

    In my query I want to specify a date and pull all the activities for that date. However, because all time stamps are UTC times, sometimes an individual's activies fall on two consecutive days in UTC time zone even though the individual completed them on the same day in his/her time zone. As a result, I get misleading data.

    When I specify a date, I want that date to be considered user's date. That means, I need to convert all the activity time stamps into user's time zone in my query. This is where I can use some help.


    Thanks, Sam
    Tuesday, October 26, 2010 2:13 AM

Answers

  • If you need to have time-zone specific date/times perhaps you could maintain a table of countries/states and offsets/daylight savings change dates etc. Then look up the offset as a combination of the time-zone offset+/-daylight savings offset. Also since you are using .Net does it include functionality to handle these multiple time zone dates that you can use to maintain your dates in the database, it may do a better job than SQL Server can?

     

    Thanks

    • Marked as answer by imsam67 Tuesday, October 26, 2010 3:33 AM
    Tuesday, October 26, 2010 3:25 AM

All replies

  • In SQL Server 2008 you can read about the new datetime types and functions here...

    http://msdn.microsoft.com/en-us/library/ms186724.aspx

    There are functions such as SWITCHOFFSET that might do what you want, you need to provide the time-zone offset, i.e. +8:00 etc. You can read more about it here in this article...

    http://www.databasejournal.com/features/mssql/article.php/3754961/SQL-Server-2008-Date-Functions-Part-2.htm

     

     

    Thanks

    • Proposed as answer by moort Tuesday, October 26, 2010 3:01 AM
    Tuesday, October 26, 2010 3:01 AM
  • Moort,

    Thank you for your response. I read about the SwitchOffset function but here's my problem: Not all countries follow the same standards when it comes to using daylight savings time and standard time -- as a matter of fact, not even all the states in the U.S. follow the same standards.

    So, I'll need another way to figure out what the offset needs to be. For example, in the next two weeks, here in the U.S. we'll be switching back to standard time but not all countries will do it at the same time.

    I guess, there's no way to do this exclusively within SQL Server.


    Thanks, Sam
    Tuesday, October 26, 2010 3:10 AM
  • If you need to have time-zone specific date/times perhaps you could maintain a table of countries/states and offsets/daylight savings change dates etc. Then look up the offset as a combination of the time-zone offset+/-daylight savings offset. Also since you are using .Net does it include functionality to handle these multiple time zone dates that you can use to maintain your dates in the database, it may do a better job than SQL Server can?

     

    Thanks

    • Marked as answer by imsam67 Tuesday, October 26, 2010 3:33 AM
    Tuesday, October 26, 2010 3:25 AM
  • I already am saving all times in UTC which means it's not affected by differences in when countries switch back and forth between standard and daylight savings times. Thanks to your response, I realized the logical error I was making in my thinking.

     

     


    Thanks, Sam
    Tuesday, October 26, 2010 3:26 AM
  • Glad it helped. ;)
    • Marked as answer by imsam67 Tuesday, October 26, 2010 3:33 AM
    • Unmarked as answer by Naomi N Sunday, May 4, 2014 4:15 AM
    Tuesday, October 26, 2010 3:32 AM
  • Thank you all very much
    Thanks, Sam
    Tuesday, October 26, 2010 3:33 AM