Query Temporal Data (pp 176-192)

Temporal Tables (see bottom of page)
Options to Retrieve SQL Server Temporal Table and History Data
MS SQL TIPS (Temporal Tables)

  SELECT TOP (1000) *
  FROM [70-176].[hr].[employees]
  for SYSTEM_TIME AS OF '2019-08-20 00:10:13.0888597'    -- validTo 2nd amend NULL; includes validFROM end point  NOT END
    --  >= ... < but not equal to - end point
SELECT TOP (1000) *
  FROM [70-176].[hr].[employees]
  for system_time all  -- all CURRENT and HISTORY
SELECT TOP (1000) *
  FROM [70-176].[hr].[employees]
  for system_time FROM '2019-08-20 00:10:08.0310436' TO '2019-08-20 00:10:08.0310436'  -- excludes the upper boundary of the end time;  MEANS BETWEEN the numbers NOT INCLUDING the end points
  
SELECT TOP (1000) *
  FROM [70-176].[hr].[employees]
  for system_time BETWEEN '2019-08-20 00:10:13.0888596' AND  '2019-08-20 00:10:13.0888596' -- includes the upper boundary of the end time; MEANS BETWEEN the numbers NOT INCLUDING the end points
    
SELECT TOP (1000) *
  FROM [70-176].[hr].[employees]
  for system_time CONTAINED IN ('2019-08-20' ,  '2019-08-21') 
  --  This is because CONTAINED IN only returns data from the history table.

1	AS OF	@dt >= validFrom AND @dt < validTo	SELECT TOP (1000) * FROM [70-176].[hr].[employees] for SYSTEM_TIME AS OF '2019-08-20 00:10:13.0888597' -- validTo 2nd amend NULL; includes validFROM end point NOT END -- >= ... < but not equal to - end point
2	ALL	SELECT * FROM <table> FOR SYSTEM_TIME ALL (current table plus history)	SELECT TOP (1000) * FROM [70-176].[hr].[employees] for system_time all -- all CURRENT and HISTORY
3	FROM TO	validFrom < @end AND validTo > @start	SELECT TOP (1000) * FROM [70-176].[hr].[employees] for system_time FROM '2019-08-20 00:10:08.0310436' TO '2019-08-20 00:10:08.0310436' -- excludes the upper boundary of the end time; MEANS BETWEEN the numbers NOT INCLUDING the end points
4	BETWEEN AND	validFrom <= @end AND validTo > @start	SELECT TOP (1000) * FROM [70-176].[hr].[employees] for system_time BETWEEN '2019-08-20 00:10:13.0888596' AND '2019-08-20 00:10:13.0888596' -- includes the upper boundary of the end time; MEANS BETWEEN the numbers NOT INCLUDING the end points
5	CONTAINED IN (start_datetime, end_datetime)	validFrom >= @start AND validTo <= @end	SELECT TOP (1000) * FROM [70-176].[hr].[employees] for system_time CONTAINED IN ('2019-08-20' , '2019-08-21') -- This is because CONTAINED IN only returns data from the history table.