Is Datetime2 compliant with Daylight Savings Time?


Today, I got following questions:

The documentation states that Datetime2 is unaware of the time zone.
    
Does that still guarantee that when the clock gets adjusted for
Daylight Saving Time this will not result in duplicate timestamps? I.e. is Datetime2 internally mapped to UTC?

Here is the answer:

  • Datetime2 does not guarantee that you will get a unique value you would
    have to have a unique constraint on the column. If you have several
    batches running at the exact same time on a multiprocessor system you
    may end up with duplicate values without a constraint on the column, if
    you need guaranteed unique values you need to work with
    uniqueidentifier and the NEWID function.
     
  • DateTime2 in SQL Server does not contain any information about

    the timezone of the value and whether the value is UTC or local time. 
    The SYSDATETIME, SYSUTCDATETIME,  SYSDATETIMEOFFSET built-in functions
    will adjust automatically when the Windows Operating System, which the SQL Server instance running on, adjusts the clock during
    daylight saving switch.  I.e.,  the datetime offset returned by
    SYSDATETIMEOFFSET will be changed from -08:0 to -07:00 for Pacific Time.  The
    SYSDATETIME value generated will have one hour gap when entering
    daylight saving, i.e., one hour is missing, but SYSUTCDATETIME does not.
  • My recommendation is that always store UTC time in datetime2 column or use SYSDATETIMEOFFSET data type, and handle local time issue by calling Windows/.Net API.

Comments (2)

  1. UndiFineD says:

    I am having difficulty inserting proper datetime data into my database via PHP

    language is set to english (US)

    collation is set to SQL_Latin1_General_CP1_CI_AS

    mind the double and single quotes used here.

    <?PHP

    $datetime = ‘convert(datetime’.date("m/d/Y H:i:s").’121)’;

    $query = "INSERT INTO Table (datetime) VALUES (‘".."’)";

    $result = mssql_query($query);

    ?>

  2. yaoqs@hotmail.com says:

    Hello,UndiFineD

     I am not very familar with PHP,  If you can use SQL Serve Profiler to trace the query sent to the SQ SQL SQ

    SQL Server, I may help with it.  I beleive the issue is not related to collation, but the datetime string format

    since SQL Server only support certian format of the string.