SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE SCHEMA history;
CREATE TABLE dbo.MyTemporalTable (ID int IDENTITY PRIMARY KEY,
                                  SomeString varchar(10),
                                  ValidFrom datetime2(7) NOT NULL CONSTRAINT DF_MyTemporalTable_ValidFrom DEFAULT SYSDATETIME(),
                                  ValidTo datetime2(7) NOT NULL  CONSTRAINT DF_MyTemporalTable_ValidTo DEFAULT '9999-12-31T23:59:59.9999999')
CREATE TABLE history.MyTemporalTable (ID int NOT NULL,
                                      SomeString varchar(10) NULL,
                                      ValidFrom datetime2(7) NOT NULL,
                                      ValidTo datetime2(7) NOT NULL);
DECLARE @SomeArbitraryTime datetime2(7)= SYSDATETIME();

INSERT INTO dbo.MyTemporalTable (SomeString,ValidFrom)
VALUES('abc', DATEADD(DAY, -1, @SomeArbitraryTime)),
      ('def',@SomeArbitraryTime);
INSERT INTO history.MyTemporalTable (ID,
                                     SomeString,
                                     ValidFrom,
                                     ValidTo)
VALUES(2,'xyz',DATEADD(DAY, -1, @SomeArbitraryTime),@SomeArbitraryTime);
3 rows affected

ALTER TABLE dbo.MyTemporalTable ADD PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo);
ALTER TABLE dbo.MyTemporalTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.MyTemporalTable));
SELECT *
FROM dbo.MyTemporalTable;


ID
SomeString
ValidFrom
ValidTo


1
abc
2024-03-25 12:42:35.3007712
9999-12-31 23:59:59.9999999

2
def
2024-03-26 12:42:35.3007712
9999-12-31 23:59:59.9999999


DECLARE @12HoursAgo datetime2(7) = DATEADD(HOUR, -12, SYSDATETIME());

SELECT *
FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF @12HoursAgo;


ID
SomeString
ValidFrom
ValidTo


1
abc
2024-03-25 12:42:35.3007712
9999-12-31 23:59:59.9999999

2
xyz
2024-03-25 12:42:35.3007712
2024-03-26 12:42:35.3007712


ALTER TABLE dbo.MyTemporalTable SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.MyTemporalTable;
DROP TABLE history.MyTemporalTable;

ID	SomeString	ValidFrom	ValidTo
1	abc	2024-03-25 12:42:35.3007712	9999-12-31 23:59:59.9999999
2	def	2024-03-26 12:42:35.3007712	9999-12-31 23:59:59.9999999