none
SQL databases, Date type, and timezones RRS feed

  • Question

  • Hi all;

    We're have a problem where a program making a select query is in one timezone, the database is in another, and the date comparisons are including an extra day. I'm guessing this is because saying @date < '2013-01-04' where @date is set as a parameter in the UK, but is for a Sql Server database in the US (Colorado), because of the timezone difference, the timeoffset part moves it into the next day.

    A DateTime in a database is fundamentally an integer (or long). So how does this work with ADO.NET when the database has a Date column value of 2013-01-04T00:00:00 - is that stored as a UTC DateTime, a local DateTime (whatever offset local happens to be at that point), or a specific offset?

    And when I set that parameter with a DateTime (Kind == Local), how does that translate?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Friday, April 12, 2013 2:18 PM

Answers

  • Hi David,

    DateTime in SQL Server database doesn't keep timezone information, it only keep the value.

    Here is a thread contains some pieces of unit test.

    http://stackoverflow.com/questions/2783348/how-is-timezone-handled-in-the-lifecycle-of-an-ado-net-sql-server-datetime-col

    An approach is to call DateTime.ToUniversal method to save UTC time to database, and call DateTime.ToLocalTime method to get the local time.

    Best regards


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by DavidThi808 Tuesday, April 16, 2013 12:49 PM
    Tuesday, April 16, 2013 8:27 AM
    Moderator
  • Adding on to Chester's response, storing local time in a datetime or datetime2 datatype is problematic because the value is ambiguous for timezones that move clocks forward and back.  To avoid this ambiguity, you must either store times as UTC or use a datetimeoffset data type to specify both the time and UTC offset.  The client can then pass time as UTC or a datetimeoffset.

    If you must store local times in the database in a datetime data type, you can convert from one timezone to on the client side but be aware you will still have ambiguity.  C# example:

    var remoteTime = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(localTime, TimeZoneInfo.Local.Id, "Mountain Standard Time");

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by DavidThi808 Tuesday, April 16, 2013 12:49 PM
    Tuesday, April 16, 2013 11:22 AM
  • But the template designer just prompts for a date (and sets it to midnight for the time). If that is set to 25 Jan 2013, does it matter if it is UTC or local time when set as a parameter in the sql? I'm thinking from what you said that no conversion takes place at that is inserted into the select as 2013-01-25 and so everything works fine.

    ??? - thanks - dave

    If you select based on date alone (midnight time), the only issue I can think of is that the UK users need to be aware of the time difference (e.g. 25 Jan 2013 is actually '2013-01-24T18:00:00 through 2013-01-25T18:00:00 MST).


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by DavidThi808 Tuesday, April 16, 2013 12:49 PM
    Tuesday, April 16, 2013 12:41 PM

All replies

  • Hi David,

    DateTime in SQL Server database doesn't keep timezone information, it only keep the value.

    Here is a thread contains some pieces of unit test.

    http://stackoverflow.com/questions/2783348/how-is-timezone-handled-in-the-lifecycle-of-an-ado-net-sql-server-datetime-col

    An approach is to call DateTime.ToUniversal method to save UTC time to database, and call DateTime.ToLocalTime method to get the local time.

    Best regards


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by DavidThi808 Tuesday, April 16, 2013 12:49 PM
    Tuesday, April 16, 2013 8:27 AM
    Moderator
  • Adding on to Chester's response, storing local time in a datetime or datetime2 datatype is problematic because the value is ambiguous for timezones that move clocks forward and back.  To avoid this ambiguity, you must either store times as UTC or use a datetimeoffset data type to specify both the time and UTC offset.  The client can then pass time as UTC or a datetimeoffset.

    If you must store local times in the database in a datetime data type, you can convert from one timezone to on the client side but be aware you will still have ambiguity.  C# example:

    var remoteTime = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(localTime, TimeZoneInfo.Local.Id, "Mountain Standard Time");

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by DavidThi808 Tuesday, April 16, 2013 12:49 PM
    Tuesday, April 16, 2013 11:22 AM
  • Hi Chester/Dan;

    That's what I thought was going on. Our problem is we're a reporting library so we have to work with what others have done. For the engine not a problem as they pass us a DateTime object as a parameter and they can make that local or UTC.

    But the template designer just prompts for a date (and sets it to midnight for the time). If that is set to 25 Jan 2013, does it matter if it is UTC or local time when set as a parameter in the sql? I'm thinking from what you said that no conversion takes place at that is inserted into the select as 2013-01-25 and so everything works fine.

    ??? - thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Tuesday, April 16, 2013 12:26 PM
  • But the template designer just prompts for a date (and sets it to midnight for the time). If that is set to 25 Jan 2013, does it matter if it is UTC or local time when set as a parameter in the sql? I'm thinking from what you said that no conversion takes place at that is inserted into the select as 2013-01-25 and so everything works fine.

    ??? - thanks - dave

    If you select based on date alone (midnight time), the only issue I can think of is that the UK users need to be aware of the time difference (e.g. 25 Jan 2013 is actually '2013-01-24T18:00:00 through 2013-01-25T18:00:00 MST).


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by DavidThi808 Tuesday, April 16, 2013 12:49 PM
    Tuesday, April 16, 2013 12:41 PM