SQL Temporal Tables Begin and End Date

Question

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.

For our BeginDate and EndDate, we want them to be date, not datetime. Always on beginning of date. So if table changes at 5/3/2018 2:30pm, SQL Temporal tables will automatically will place datetime, however, we just want date 5/3/2018. Is there anyway to only implement dates, without using Views (which convert the datetime to date), and without modifying/updating the Temporal history table? Is there a property or setting to only use regular date? If no other options, what is best out of the two, views or updating temporal history table?

CREATE TABLE dbo.Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
      CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
      CONSTRAINT DF_Department_SysEndTime 
    DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Answer 1

Since you're only wanting to use DATE values to determine your historical changes, I'm assuming the data will only be updated once per day, otherwise you will potentially have multiple historical entries with the same day value that you cannot identify an order for with only a DATE value.

Given this assumption, you can possibly rely solely on the SysStartTime for beginning and end information by querying your table and history table. To this end, if you add a computed column to your table like below:

CREATE TABLE dbo.Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START
      CONSTRAINT DF_Department_SysStartTime DEFAULT SYSUTCDATETIME() NOT NULL,
    ValidFromDate AS CAST(SysStartTime AS DATE), -- This is a computed column
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END
      CONSTRAINT DF_Department_SysEndTime 
    DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ) NOT NULL,
    PERIOD FOR SYSTEM_TIME(SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Your history table does not have the column as computed but rather persists the value at the point of any change to the record. This won't work for SysEndTime because it will not recalculate the value when it is changed by system versioning, so it is always 9999-12-31.

Queries to Department will return the computed value, which is simply the SysStartTime datetime2 value converted to a date, and queries to DepartmentHistory will return the persisted value which is the computed value as it was at the point of being versioned (which is the SysStartTime datetime2 value converted to a date).

If you can rely only on SysStartTime for your Type 2 SCD, then this should meet your requirements.