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

• Anonymous
  February 04, 2010
  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); ?>

• Anonymous
  February 05, 2010
  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.