CODEWITHSUNDEEP
sql-servertemporal-tables
Randy Minder
Randy Minder

Reputation: 48492

Temporal Tables Between Clause - Rows are included that shouldn't be?

I have a SQL Server 2016 temporal table. I executed a query which can be seen below, along with the results. I'm curious why the row highlighted in green is included. The ValidFrom value occurs before the begin date of '7/12/2018 19:16:00' and the ValidTo occurs after the end date of '7/12/2018 19:30:00'.

My understanding of BETWEEN / AND, which might be incorrect, is that it finds all rows modified between two points in time.

enter image description here

Upvotes: 2

Views: 277

Answers (1)

Laughing Vergil
Laughing Vergil

Reputation: 3756

The BETWEEN clause on a temporal table shows those rows that were active during the selected timeframe. In more conventional terms, the rows that meet this criteria set:

WHERE StartTime <= <EndDateTime> 
    AND EndTime > <StartDateTime>

As you can see, this does not require an actual time stamp to fall within your BETWEEN dates.

Upvotes: 2

Related Questions