Best practice for open application time period
In part 4 and part 5 of the temporal tables blog series, an Employees table is defined with a PERIOD FOR appl_time (startDate, endDate).
What is the recommended best practice for open application time intervals such as an employee who does not yet have an endDate?
In the blog post, a sentinel date in the future of 2039-08-21 is used. This strategy makes computations such as average tenure complicated, as we'll need logic to detect this value and replace it with the current date.
Ideally we could use some sort of conditional generated column that would take on the value NOW() when an employee is still employed. I don't think that's possible yet though.
Any guidance would be much appreciated!
Answer Answered by Daniel Black in this comment.
Application time periods come from SQL:2011 standard with rather constrains the implementation.
Even documents related to the standard like this article tend to use similar sentinel dates.
The date datatype supports up to 9999-12-31 which should support the current timelines of employment (for now). A date/datetime value that represents the equivalent of infinity which might have some merit as a feature request especially if there's a standard that can be referenced or an existing SQL implementation.
Start date columns can take a default value of now (or any of its aliases).
