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