1
SELECT *
2
FROM dbo.MyTable
3

4
--( at time zone does not work on literals, use var to work around this limitation )--
5
DECLARE @AsOf DATETIME2 = '4/8/2019 9:43AM'
6
SELECT @AsOf = @AsOf AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC'
7

8
SELECT *
9
FROM dbo.MyTable
10
FOR SYSTEM_TIME AS OF @AsOf --( includes changes where the record was valid during the given time )--
11
--FOR SYSTEM_TIME ALL --( includes all changes )--
12
--FOR SYSTEM_TIME FROM @StartDate TO @EndDate --( exclusive of start and end dates )--
13
--FOR SYSTEM_TIME BETWEEN @StartDate AND @EndDate --( inclusive of start and end dates )--
14

15
--( query directly from the history table )--
16
SELECT *
17
FROM dbo.MyTableHistory

1
DECLARE
2
  @TableName SYSNAME = 'MyTable',
3
  @TableSchema SYSNAME = 'dbo',
4
  @IsDebug BIT = 0
5

6
DECLARE @Sql NVARCHAR(MAX) = ''
7

8
--( add sys version columns and enable )--
9
SELECT @Sql = '
10
ALTER TABLE [{0}].[{1}]
11
ADD [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DF[{0}]].[{1}]].[ValidFrom]]]  DEFAULT (sysutcdatetime()),
12
[ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DF[{0}]].[{1}]].[ValidTo]]] DEFAULT (CONVERT([datetime2](0),''9999-12-31 23:59:59'')),
13
PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
14

15
ALTER TABLE [{0}].[{1}]
16
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [{0}].[{1}History]));
17
'
18
SELECT @Sql = REPLACE(@Sql, '{0}', @TableSchema)
19
SELECT @Sql = REPLACE(@Sql, '{1}', @TableName)
20

21
IF @IsDebug = 1
22
  PRINT (@Sql)
23
IF @IsDebug = 0
24
  EXEC (@Sql)

1
DECLARE
2
  @TableName SYSNAME = 'MyTable',
3
  @TableSchema SYSNAME = 'dbo',
4
  @IsDebug BIT = 0
5

6
DECLARE @Sql NVARCHAR(MAX) = ''
7

8
SELECT @Sql = '
9
ALTER TABLE [{0}].[{1}]
10
SET (SYSTEM_VERSIONING = OFF)
11
'
12
SELECT @Sql = REPLACE(@Sql, '{0}', @TableSchema)
13
SELECT @Sql = REPLACE(@Sql, '{1}', @TableName)
14

15
IF @IsDebug = 1
16
  PRINT (@Sql)
17
IF @IsDebug = 0
18
  EXEC (@Sql)