Use of datetime2 on SQL Server instead of datetime

Wiki (en)
1

The Invantive SQL data type “datetime” was traditionally implemented on SQL Server data containers using the SQL Server data type “datetime”.

The SQL Server data type “datetime” however has some limitations, such as:

  • Covers only dates after January 1, 1753 when Great Britain had switched to Gregorian calendar.
  • Maximum accuracy of approximately 3 ms.
  • No time zone support.

Microsoft SQL Server introduced in SQL Server 2008 the data type datetime2 to compensate for the first two shortcomings. The datetime2 allows dates starting at January 1, 1 which - despite maybe not always giving the expected outcome for all regions - provides more precision than previously. Also, the accuracy has been improved to 100 ns.

The datetime2 format has no time zone support. Note that the SQL Server timestamp data type bears no resemblance with timestamps on other RDBMS systems and is just used for row versioning. Instead, SQL Server provides time zone support through datetimeoffset.

Starting release 20.2.243, Invantive SQL will prefer the use of datetime2 over datetime when exchange data with SQL Server. For instance, an Invantive SQL statement like:

create or replace table new_table@sqlserver
as
select *
from   exchangerates@ecb

will create a SQL Server table with the datetime columns represented in the backing SQL Server database as datetime2 instead of datetime. The amount of storage needed for datetime2 compared to datetime does not significantly change. The datetime2 data type has some limitations compared to the datetime data type, such as having no support on SQL Server itself for adding a number of days. However, Invantive SQL provides these arithmetic operations.

The Invantive SQL’s internal storage format will remain unchanged with a precision of 100 ns and a value range between January 1, 1 and December 31, 9999.

Migration from datetime to datetime2

All tables re-created using Invantive SQL will automatically switch to datetime2.

In case a column in a table needs to be changed from datetime to datetime2 to gain access to the new capabilities, a SQL Server statement can be used like:

alter table table_name alter column date_column datetime2

Such use of altering columns places a number of requirements on the database such as use in views. Please consult your local DBA for advice.