You can use the new FOR SYSTEM_TIME clause to retrieve temporal data that was or is valid between two points in time. These queries are typically used for getting changes to specific rows over time. To achieve this, you could use one of two SQL:2011 standard-specified sub-clauses:

• FROM…TO returns all data that started before or at the beginning of a given period and ended after the end of the period (closed-open interval)
• BETWEEN…AND returns all data that started before or at the beginning of a given period and ended after or at the end of the period (closed-closed interval)

As you can see, the only difference between these two sub-clauses is how data with a starting date to the right side of the given period is interpreted: BETWEEN includes this data, FROM…TO does not.

The following queries demonstrate the usage of these two sub-clauses and the difference between them:

--example using FROM/TO 
SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo
FROM Application.People FOR SYSTEM_TIME FROM '2016-03-20 08:00:00' TO '2016-05-31 23:14:00' WHERE PersonID = 7; 
 
--example using BETWEEN 
SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo
FROM Application.People FOR SYSTEM_TIME BETWEEN '2016-03-20 08:00:01' AND '2016-05-31 23:14:00' WHERE PersonID = 7; 

Here are the result sets generated by the preceding queries:

As you can see, the second query returns three rows, as it includes the row where the start date is equal to the value of the right boundary of the given period.

These two sub-clauses return row versions that overlap with a specified period. If you need to return rows that existed within specified period boundaries, you need to use another extension, CONTAINED IN. This extension (an implementation of one of Allen's operators) is not defined in the SQL:2011 standard; it is implemented in SQL Server 2017. Rows that either start or end outside a given interval will not be part of a result set when the CONTAINED IN sub-clause is used. When you replace the sub-clause BETWEEN with it in the previous example, only rows whose whole life belongs to the given interval will survive:

SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo
FROM Application.People FOR SYSTEM_TIME CONTAINED IN ('2016-03-20 08:00:01','2016-05-31 23:14:00') WHERE PersonID = 7; 

Instead of three rows using BETWEEN, or two with FROM…TO sub-clauses, this time only one row is returned:

Although this extension is not standard, its implementation in SQL Server 2017 is welcomed; it covers a reasonable and not-so-rare use case, and simplifies the development of database solutions based on temporal tables.