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));