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.