locked
SQL Azure DateTime (UTC vs localtime values) RRS feed

  • Question

  • Discovered a basic issue and not sure how to best deal with it.

    I'm in Eastern Time zone in US.  I've got a routine AddItemToLog(string actiontaken) which gets called by an Azure Web Service.  The log has a DateTime2 field which indicates the datetime value of when item was added to a list.  When called, I am setting value as part of the webrole script.

    I've got Entity Framework, DataSets, and DBML as methods to connect / update data.  Currently using dbml

    newlogitem.DATETIME_CHECKED = DateTime.Now ;

    or I've tried

    newlogitem.DATETIME_CHECKED = DateTime.Now.ToLocalTime() ;

    newlogitem.ACTIONTAKEN = actiontaken ;

    My issue is that when triggered from my client, it saves a record and associates the current DateTime as the DateLogged.  This works fine in my emulator because my host emulator and client are in the same timezone.

    I am finding that after I publish to azure, time value saved is in UTC format or 3 hours ahead.  This is confusing when viewing raw data from a query I am seeing datetimes in the future.  

    Is there a way to force display on portal to show in local time during query results?  Can I turn of using UTC times when saving and just save in local time?  On the client side, when data is retrieve, I just want to display the correct time (e.g. whateven the time value was).

    Any advice is much appreciated.


    Ed

    Wednesday, September 19, 2012 2:20 PM

Answers

All replies

  • Hi Fast_EddieD,

    Welcome to MSDN Forum.

    Please refer to this article, it provides a solution may help you. : )

    By the way, this issue seems related to SQL Azure, you can also post in this forum for better help: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/threads

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us


    • Edited by Allen_MSDN Thursday, September 20, 2012 1:55 AM
    • Marked as answer by Fast_EddieD Friday, September 21, 2012 2:44 PM
    Thursday, September 20, 2012 1:54 AM
  • OK, I now understand and will specify DateTime.Now or DateTime.UTCNow when saving.

    Currently, everything is working in comparisons but when I do a simple SQL Query in Azure like...

    Select * from [My_Log] order by Date_Posted Desc

    The Date is displayed in UTC value.  When I retrieve and view on the client app, it displays local time (correct).  Is there a way to show local time in SQL Azure query results window? 

    Guess I am wondering if I can I substitute something like select Date_Posted.ToLocalTime() from ... in SQL (not sure of syntax).  If so, which time would be displayed.  Localtime of Azure Server I am connected to? 

    I've got a situation where my Azure and SQL Azure accounts are hosted in different regions (.  I'm not sure if US regions use different time zones but as you can see, this is really getting confusing...

    Thanks for your info.


    Ed

    Friday, September 21, 2012 2:44 PM